Need help specifying condition of referenced action

[Pratyay_Rakshit]

Cutting straight to the chase, I have a User bill submissions form which records data such as Supplier Name, Bill Number, Bill Amount and Project Name.

I wanted to select multiple unpaid bills at once, sum them and go to the main company expense form for payment. A kind person, steve, helped me out in writing the expressions necessary to do that. The summed amount, supplier name is stored in an intermediary Supplier payment form which when saved, takes to the company expenses form.

Steve’s directions worked like a charm and I have been able to do until that point.

Now, what I want, is to mark the bills that are paid, as Paid. That requires actions to be properly set up. I have thought of the following workflow:

1. Build an action for the ‘User Bill Submissions’ table that marks the Paid? column to Paid.

2. Build an action in the “Supplier Payment Form” that references the “User bill submissions” table, with the referenced Rows set as [Bill Number]. ( Actually this is the part I’m to figure out, probably it’s going to need some tinkering with select() expressions again )

3. The “Supplier Payment form” already had an action ( using linktoform() ) that takes the user to the main company expense form when saved. Now the new action that is incorporated has to be somehow made to work in unison with the previous action. For that I define a new action that’s definition is “Execute a series of actions” and include the two actions within it.

I need help with my concern expressed in point 2 . Maybe the other points are in line with the correct way, I suppose.

The action to mark bills paid :

The action in the Supplier payment form that will reference the above action

The action to navigate to the company expense form

Finally the grouped action

Please help. I asked this in the same thread I created for people to help me out with the payment mechanism, but have happened to get no answer. If Steve, you see it, please don’t mind.

[Steve · Accepted solution]

You’ll recall the expression used to sum the bill values:

SUM(
  SELECT(
    User bill submissions[Bill Value],
    IN([Bill Number], [_THISROW].[Bill number])
  )
)

The Referenced Rows expression would be very similar:

FILTER(
  "User bill submissions",
  IN([Bill Number], [_THISROW].[Bill number])
)

Note also a limitation of grouped actions: a navigation action, such as Navigate to company expenses, must be the last action. Any actions listed after a navigation action will not be performed.

[Pratyay_Rakshit]

Thanks again ❤️

[Pratyay_Rakshit]

Hey @Steve I happened to screw up.

I was tinkering with the mechanism and couldn’t get it to work.

So I restored everything back to where it was running correctly, but I can’t make the Status to update. The navigation action does trigger though.

I can’t figure out why. Can you help?

[Pratyay_Rakshit]

It was a problem with the key column. For a first number of days I didn’t have the key column, and when the number of entries grew close to a hundred, problems started showing up and I had to incorporate the key column.

I had prefilled the existing ones by the sheet formula randbetween() and it has a weird characteristic that every time a new row is added, the number changes.

So a static key being not present, the expression couldn’t actually ‘find’ what rows to act on.

I just copied those values, ommitted the formula randbetween and pasted the values as plain text. Now it works.