1. Forum
  2. Usenet
  3. COMP.SYS.ACORN.APPS

  • Pipedream lookup error in custom function

    From Harriet Bazley@21:1/5 to All on Fri Apr 11 20:46:49 2025
    I have a fairly basic Pipedream custom function that adds up the total
    of all the items that match an arbitrary list of categories (items of expenditure that come under the heading of 'food'). But it gives an
    error if any of the categories in the list are not found in the input,
    because the lookup function fails.

    Typical usage:
    edibles(array1,array2), where array 1 and array2 are non-consecutive
    database columns containing categories of expenditure (e.g. Transport,
    Grocery, Clothes) and totals spent under those categories. I just want
    to be able to add up the subtotal of the amount spent on edible items specifically.

    But how do I 'blank' the input so that a non-existent category is
    counted as having a value of zero, rather than throwing an error?
    Currently I have to manually add an extra row to the input arrays, e.g. "Dairy", 0, in order to suppress the error when in occurs.

    I feel that it ought to be possible to have some kind of 'if' option
    that would return zero if the lookup fails, but I can't work out a way
    of doing it.)

    =================================

    Add up the total value of all the entries in array 2 which correspond to entries in array 1 that match one of the text values specified in this function. ------------------------------------------------------------------------ ...function("edibles","categories:array","prices:array") ...lookup(C7,@categories,@prices) Dairy ...lookup(C8,@categories,@prices) Greengrocery ...lookup(C9,@categories,@prices) Grocery ...lookup(C10,@categories,@prices) Meat
    ...result(sum(A7A10))


    --
    Harriet Bazley == Loyaulte me lie ==

    It is better to have loved and lost than just to have lost.

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From "Richard Torrens (News@21:1/5 to Harriet Bazley on Sun Apr 13 11:42:33 2025
    In article <[email protected]>,
    Harriet Bazley <[email protected]> wrote:
    I have a fairly basic Pipedream custom function that adds up the total
    of all the items that match an arbitrary list of categories (items of expenditure that come under the heading of 'food'). But it gives an
    error if any of the categories in the list are not found in the input, because the lookup function fails.

    Typical usage:
    edibles(array1,array2), where array 1 and array2 are non-consecutive
    database columns containing categories of expenditure (e.g. Transport, Grocery, Clothes) and totals spent under those categories. I just want
    to be able to add up the subtotal of the amount spent on edible items specifically.

    But how do I 'blank' the input so that a non-existent category is
    counted as having a value of zero, rather than throwing an error?
    Currently I have to manually add an extra row to the input arrays, e.g. "Dairy", 0, in order to suppress the error when in occurs.

    I feel that it ought to be possible to have some kind of 'if' option
    that would return zero if the lookup fails, but I can't work out a way
    of doing it.)

    =================================

    Add up the total value of all the entries in array 2 which correspond to entries in array 1 that match one of the text values specified in this function. ------------------------------------------------------------------------ ...function("edibles","categories:array","prices:array") ...lookup(C7,@categories,@prices) Dairy ...lookup(C8,@categories,@prices) Greengrocery ...lookup(C9,@categories,@prices) Grocery ...lookup(C10,@categories,@prices) Meat
    ...result(sum(A7A10))

    I think you need to use dsum()

    --
    ------------------------------------------------------------------
    Richard Torrens.

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)
  • From Harriet Bazley@21:1/5 to All on Sun Apr 13 18:14:21 2025
    On 13 Apr 2025 as I do recall,
    Richard Torrens (News) wrote:

    In article <[email protected]>,
    Harriet Bazley <[email protected]> wrote:

    [snip]


    I feel that it ought to be possible to have some kind of 'if' option
    that would return zero if the lookup fails, but I can't work out a way
    of doing it.)

    =================================

    Add up the total value of all the entries in array 2 which correspond to entries in array 1 that match one of the text values specified in this function. ------------------------------------------------------------------------ ...function("edibles","categories:array","prices:array") ...lookup(C7,@categories,@prices) Dairy ...lookup(C8,@categories,@prices) Greengrocery ...lookup(C9,@categories,@prices) Grocery ...lookup(C10,@categories,@prices) Meat
    ...result(sum(A7A10))

    I think you need to use dsum()


    Hmmm. dsum() is what is creating these summary blocks in the first
    place (listcount to analyse the categories present in a very long list,
    then dsum() replicated downwards to add up the values corresponding to
    each of those categories).

    But I don't see how to use it to look up the numeric value in one array corresponding to the textual value in a parallel array....
    All I actually want to do here is to add up an arbitrary subset of the
    values, but where I don't know in advance which values are necessarily
    going to be present or which rows they are going to be located on -
    since that depends on the output of listcount().

    So I might have a block that looks like

    Dairy 1 0.59
    Entertainment 2 61.50
    Greengrocery 5 64.95
    Grocery 4 17.00
    Household 1 5.00
    Meat 3 12.60
    Music 2 508.76
    Postage 1 3.95
    Repairs 2 271.00
    Transport 1 3.50
    Utilities 5 146.27

    or one that looks like

    Entertainment 1 10.00
    Greengrocery 5 81.91
    Grocery 3 11.00
    Insurance 1 124.09
    Meat 2 15.50
    Music 2 110.00
    Tax 1 226.85
    Utilities 5 128.24

    but I want to return the sum of the values for however many of Meat,
    Dairy, Grocery and Greengrocery happen to be present.


    --
    Harriet Bazley == Loyaulte me lie ==

    Sir Francis Drake circumcised the world with a 100-foot clipper.

    --- SoupGate-Win32 v1.05
    * Origin: fsxNet Usenet Gateway (21:1/5)