How do I 'set the values of some columns in this row' with values from another table?

[Pratyay_Rakshit]

I have a referenced action running for one table.

And the action that is ‘referenced’ is of the set the values of some columns in this row type. The value that is to be set by the action lets us input expressions, so that’s hope for me.

So what I want is to set the value to the one that I input in a row of one of the columns of the parent table. What’s the expression to do that?

[WillowMobileSys]

There are a couple of ways:

You mentioned “Parent” row. If you have a parent/Child relationship between the two rows then this means you have the Parent ID on your child row - as reference to the parent row. If that is the case then all you need to do to get the value from the parent is use “dot” notation as in this expression:

[Child Parent Ref Column].[Parent Column for value]

If you don’t have the parent/Child relationship, meaning no Parent reference on the row you are updating. you can still get the value you want by using a LOOKUP() function.

help.appsheet.com

LOOKUP()

Get column value from row in table or slice

[Pratyay_Rakshit]

Will update you if I get this to work.

[Pratyay_Rakshit]

This is a bit off context, but is there a need to use actions at all in this case, if the lookup() function is used?

The action would set the value of a row to something the lookup() function returns. What if I just put the lookup () function in the column formula? It would update as the column from where the value is ‘looked up’ is updated. Isn’t it?

[WillowMobileSys]

You haven’t really given much description on what you are actually trying to accomplish. But yes there are many ways to pull in values you might use to update a column in a Form or simply show in a view WITHOUT the need for an Action.

Below are a few of these. I’m sure there are more.

If you have a true parent/child relationship (used the “Is part of” switch and have proper key columns between the tables) then you can access values between those two tables freely using the Ref columns and “dot” notation.

Also, without parent/child relationship, you can pull values from another table IF you have some way to link them using a LOOKUP table. For example, an Order Detail row and an Inventory row usually identify the same Product but you wouldn’t use a Parent/child relationship to connect these tables. But you can use the LOOKUP() functions to pull in, say, the Inventory counts into the Order Detail record so you know how much is On Hand, On Order, allocated, ect.

Lastly, you could use a LOOKUP() if you “know” certain values by hard coding them. For example, I might have a Status table where I keep counts of something in various status’. Maybe a status value is “Done” that I want info for. I can use a LOOKUP() to retrieve by that hard coded value.

[Pratyay_Rakshit]

Okay here’s the details:

See that ‘Pay part from unpaid’ tab?
It when clicked, gives an option to enter the amount rather than automatically update the amount the bill had, should I had chosen a full bill payment option. Like this:

The bill numbers are shown by valid_if expressions from a main table where users enter the bill details when bills reach respective project sites. One condition in that valid if is the bill status has to be ‘Unpaid’. When I choose to pay bills in full, (i.e. the scenario in the second image) the status of the bill goes from unpaid to paid in the main table. @Steve assisted me greatly in figuring out that.

Now there are times when I don’t want to pay bills in full but only a part of them. For that I incorporated the mechanism.

Now the valid_if is set up perfectly, it shows the unpaid bills to choose from. (But here the type is enum as opposed to enumlist in the ‘full payment scenario’ as it doesn’t make sense to pay multiple bills in part).

Theres a field below that lets me enter the amount that I want to actually pay. It gets added to the bill amounts, had I chosen any full bills to pay and an action navigates me to the main expense form.

Now I want to mark the corresponding status in the main ‘bill form’ from unpaid to partially paid and update the partial payment amount in a column. That requires a grouped action, one to mark the bill from unpaid to partially paid, and the other to update the payment amount.

It would be worthy to mention that I had already been running another grouped action to mark unpaid bills to ‘paid’ and navigate to company expense form.

Now the new actions (that I would like to fire only when I’m partially paying bills) have a behavior when the condition is true that “Partial payment option” is equal to “Pay part from full” (See 1st image).

The action to set the partial payment amount in the main “User bill submissions” table is:

The closeup of the area marked in red, i.e. the expression:

The terms in the expression will make sense if you look at the very 1st image in this post.

The column “Computed” is a virtual column which is the concatenation of the “Bill Number” and “Bill Amount”.

I now grouped these two actions into one.

And used this action as a referenced action in the main “user bill submissions” table.

The ‘referenced rows’ expression is (this is something that @Steve helped me out with earlier. I just copied)

And finally make another grouped action:

The 3rd action is a group of the other three. The navigation action is at last.

While the action to mark bills from Unpaid to Paid and navigating to company expense form was running correctly, I can’t get this new set of actions to work. It doesn’t even mark the bills from unpaid to partially paid.

I hope this is enough information. I am happy to provide more if needed.

[WillowMobileSys]

First, It seems you are using the column [Computed] when you want to compare to [Bill Number]. Is this intentional? If so, I would expect that [Computed] then has some Bill Number values in it?

Let’s get the Action working first then we can return to your original question.

[Pratyay_Rakshit]

Looks like I screwed up the actions that were running previously correctly, as well.

The computed column is a virtual column, with formula as concatenation of the bill number and bill amount. If the bill number is ABC and the amount is 123, then the ‘computed’ column should return ABC:123

Yes thats intentional as I want to see the bill number as well as the amount when choosing from the list.

[WillowMobileSys]

Ok, so to back up.

1. Are the changes getting Saved?
2. Have you attached the Grouped Action to the “Form Saved” Behavior?
3. Is there any criteria in the Grouped Actions “Only if this condition is true” value that would prevent the Group from running?
4. You might try checking the Log file for errors. You can find this by expanding a Workflow and then tapping on the “Log” button in the header of that Workflow.

If everything looks ok above, then I would start reducing the problem. For example, use just a single, simple Action in the Form Save property. Keep reducing until things work OR you figure out what the problem is. Then start building back up again.

[Pratyay_Rakshit]

Looks like I screwed up the actions that were running previously correctly, as well.

I managed to fix this. 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.

We can now proceed to the actual problem.

[WillowMobileSys]

Pratyay_Rakshit:

So what I want is to set the value to the one that I input in a row of one of the columns of the parent table. What’s the expression to do that?

This was your original problem. Correct? I presume this is that partial payment amount that you want to record in the other table?

[Pratyay_Rakshit]

Yes.

[WillowMobileSys]

Ok. I’m going to provide what I think is a more efficient solution and then you can tell me if it can or cannot work for you and then we’ll go from there.

I would make the “User bill submissions” table a child of the “Supplier payment form” table. When a user chooses to make a partial payment, you can then show an inline table to Add the partial payment directly to the “User bill submissions” table.

Not only does this reduce processing complexity, bu this has the added benefit of being able to automatically see ALL of the partial payments being made.

How does that sound to you?

[Pratyay_Rakshit]

How is this going to affect the already existent mechanisms? I think it should be the other way around, i.e. the ‘Supplier payment table’ set up as a child of the ‘user bill submissions’ table.

It’s because the supplier payment table ‘fetches’ data from the user bill submissions table, not the other way around.

[WillowMobileSys]

According to what i’m seeing in your actions, you are writing info INTO “User bill submissions” FROM “Supplier payment form”. Correct?

I actually believe you can eliminate the Actions to set the status’ and write the payment amount (if I understand your data structure correctly). You only need the Action to navigate to the “expenses” view.

If you want to keep the flow you have built that is an alternative way to do it as well. It looks like you have the action “Set partial payment amount and mark partially paid (group)” Action. I guess i need to know with which part you are struggling with here.

[Pratyay_Rakshit]

Actually I deleted every one of the new actions I set up in a bid to smoke the error out. When they were there, no action in the group seemed to work. Now there is no way to know if not I build them again, whether one of them was working or not.

Also I learned that there is no need to make a group of actions to do what the Set partial payment amount and mark partially paid (group) action was doing, as the two actions that are grouped are both of the same type, i.e. set the column values to something.

Should I try again once and update you if that works or not?

[WillowMobileSys]

Since you are kind of starting over, here are a couple of things to think about.

1. How are you planning to show the multiple partial payments? Somewhere someone will want to see what partial payments have been made against the bill.

2. How do you deal with editing of these partial payments?

The child/inline table helps mitigate these concerns for you and it leans to a more efficient process for you as the developer. It is not full-proof. And it boils down to whether you like the look and feel or not.

If you want to try the “Child” approach, I am willing to help. It should only take a few minutes to get it setup.

Otherwise, if you think the Actions approach is a better fit, then please go ahead and re-build, applying your new knowledge, and then re-post with any issues or questions. I’ll be here and of course others might jump in as well.

[Pratyay_Rakshit]

I want to try the ‘child’ approach. Lets.

[Pratyay_Rakshit]

But I did try everything building from scratch, and the status does get updated from unpaid to partially paid.

But the payment amount doesn’t seem to update.

[WillowMobileSys]

Show the part where you are updating the payment amount.

[Pratyay_Rakshit]

This is the update expression. I guess you want this, or some other thing?

The form.

The contingent part of the spreadsheet.

[WillowMobileSys]

I needed to see that. Now i need to see where you are applying it. Its in an Action? So just show that Action definition.

[Pratyay_Rakshit]

This is referenced to:

The referenced rows expression:

[WillowMobileSys · Accepted solution]

It looks like there are some changes in column names from before, so I am a little confused on how you are matching up bill rows.

Bottom line is it doesn’t look like the LOOKUP() has the right column references in it to properly match rows.

It should be something like this:

LOOKUP([Column name of bill ID in "User bill submission"],
       "Supplier payment form",
       "Column name of bill ID in Supplier payment form",
       "Enter Amount" )

[Pratyay_Rakshit]

I don’t know how much to thank you. I do want to try out that child approach very soon. I have a lot of implementation ideas in mind that I’d rather would be very much fitting to the child approach. Will get back to you soon on this.

Also can I keep discussing a few more aspects in this current agenda I have taken to incorporate this ‘partial payment mechanism’ in the app ?

There are a few more things left, like devising the mechanism when an user would choose the ‘pay full from partially paid bills’ option. It would be quite similar to this, but I don’t know how much problem I’m going to run into, and making someone else understand all of this from scratch is way difficult than what we have going on here.

[WillowMobileSys]

Sure, I’ll be around. Feel free to send a private message if you prefer.

[Pratyay_Rakshit]

Cool