How to do "LookupOne with CONTAINS"

Ask for Help
1

Hi there,

I saw Grist on HN yesterday and thought it would be a good exercise to port my family financial database over as a trial run.

I just ran into a road block with LookupOne.

I have two tables: transactions and categories. I’m trying to automatically populate transactions.payee with the first payees record where transactions.Description CONTAINS the payees.Pattern. In python code, it’d look like this:

for payee in payees:
    payee.Pattern in $Description and return payee or continue

The formula I used was Payees.lookupOne(Description=CONTAINS($Payee.Pattern)) but looks like it’s not quite what the grist-core expects.

What am I doing wrong?

Thanks for creating grist. I like what I see. Can’t wait to port a few other internal apps over.

Alex

Screen Shot 2022-02-20 at 08.41.48
Screen Shot 2022-02-20 at 08.41.481920×1166 267 KB

2

The CircularRefError is because the formula for Payee is being asked to use the value of Payee itself, while it’s still being calculated.

In any case, CONTAINS can’t be used like this:

Note that the column being looked up (e.g. genre ) must have values of a container type such as list, tuple, or set. In particular the values mustn’t be strings, e.g. "Comedy-Drama" won’t match even though "Drama" in "Comedy-Drama" is True in Python.

You will have to do something like your original idea:

for payee in Payees.all:
    if payee.Pattern in $Description:
        return payee
2 Likes