I have one table which has information posted...

(Jaya Dhiwar) #1

I have one table which has information posted by form. now i want same information should be updated once we push any data by that form in other source of table, how to achive this?

(Steven Coile) #2

Sounds like you want a workflow, triggered by an addition to the first table, that invokes an action against a set of rows in the second table, that modifies the data in those rows.

(Aleksi Alkio) #3

@Jaya_Dhiwar

Have you thought about virtual column for that purpose?

(Steven Coile) #4

@John_Baer1 I think it’s possible to affect a different table, but it’s not trivial.

@Aleksi_Alkio’s suggestion is a good one. Note that the virtual column would not be in the table your modifying, but in the table you want to update. In effect the virtual column would be an auto-updating column. When you make the change to the first table, the virtual column in the second table would see the change in the first and update automatically to reflect the change.

(John Baer) #5

+Steve Coile Ah-Ha!

Light bulb!

That’s what I was missing.

Thanks!

Off to try again!

(Steven Coile) #6

@John_Baer1 let us know how it works out!

(John Baer) #7

+Steve Coile @Aleksi_Alkio

Ok, so I had trouble with Virtual Column approach.

In my use case, I want to update a value in Table 2 based on a new row insertion in the Table 1.

In the Virtual Column, I could determine when there were new rows in Table 1 but wasn’t sure where to go from there - i.e. in the App Formula linking the correct rows and then performing the math.

However,

I think I ended up going the non-trivial approach.

I was able to achieve what i needed with a group of actions activated on Form Save.

The key is using the option “execute an action on a set of rows” on Table 1 which allows activating an Action against Table 2 (Referenced Table, Rows and Action).

While this solution seems more intuitive,

I would still like to look deeper into the Virtual Column approach.

I’m sure there are some gotcha’s I haven’t hit yet but it is looking good.

If either of you think I am over-complicating, please chime in.

(Steven Coile) #8

@John_Baer1 Bingo! Well done!

(Aleksi Alkio) #9

If I have understood your request, you would like to read always the latest row’s value(s) in another table. Is this correct?

(John Baer) #10

@Aleksi_Alkio I would say yes.

Initially the intent is that this is a “one and done” type transaction.

The record then becomes read only and possibly the ability to “undo”.

There are likely edge cases where a re-process might be needed.

So, I would say yes, take latest value from Table 1 and apply it to Table 2.

(Aleksi Alkio) #11

Is this table 2 just one row table?

(John Baer) #12

@Aleksi_Alkio Yes, that is correct.

(Aleksi Alkio) #13

Add a virtual column with the MAXROW expression. The result of this expression is key column’s value. Then you can read the latest row where the [KeyColumn]=[MaxRowColumn] for Table 2.

(John Baer) #14

@Aleksi_Alkio How can we use a virtual column for this use case?

I too am looking for a similar activity - updating a second table based on entries in first.

I understand the workflow suggestion by +Steve Coile, but that happens in backend and requires a user manual sync to see the change.

An ok solution.

(Aleksi Alkio) #15

If you write the formula into the virtual column, it will read the value for example from another table without opening/saving the record. You can read more about the virtual column from this article… help.appsheet.com - Virtual Columns Virtual Columns help.appsheet.com

(John Baer) #16

@Aleksi_Alkio Yes, but how to update that Virtual Column upon the Save action?

(Aleksi Alkio) #17

The app formula will do that.

(John Baer) #18

Awesome!

I’m giving it a try now!

(John Baer) #19

@Aleksi_Alkio So, I gave it a try and I guess I don’t know the proper App formula.

I can use LOOKUP() to retrieve the value and apply changes to it in the view.

What do I use in App Formula to update the referenced value in the Virtual Column?

(John Baer) #20

@Aleksi_Alkio Thank you for your help with this.

I know you post a LOT of helpful tips and solutions.

I for one am very grateful for all that you do for us!!

I am still baffled by this particular problem.

I must not know the correct App Formula to use…yet.

Could you please provide an example of an App Formula used in a Virtual Column that references and updates a value in another table?