Need help specifying condition of referenced action

AppSheet AppSheet Q&A
, ,
1

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 :

2X_c_c3535c5d26e66b20ca853db5534e94bab4385e8d.png
2X_c_c3535c5d26e66b20ca853db5534e94bab4385e8d.png866×348 16.3 KB

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

2X_3_30c3667ca90c1ad5633a7f9e4411f2288ce11e7e.png
2X_3_30c3667ca90c1ad5633a7f9e4411f2288ce11e7e.png882×441 21 KB

The action to navigate to the company expense form

2X_d_d7257f9730d850715471c5a86f6f6d3734340a8a.png
2X_d_d7257f9730d850715471c5a86f6f6d3734340a8a.png851×292 16.7 KB

Finally the grouped action

2X_0_0d798c7bf66db3dde3991678b154c27d4e82dd89.png
2X_0_0d798c7bf66db3dde3991678b154c27d4e82dd89.png854×353 16.6 KB

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.

2

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.

2 Likes
3

Thanks again <3

1 Like
4

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?

5

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.

1 Like