Data Entry in two tables at a time

AppSheet AppSheet Q&A
1

3X_c_d_cd9662044cd3882fdf580c1321ecbbdecf6922af.jpeg
3X_c_d_cd9662044cd3882fdf580c1321ecbbdecf6922af.jpeg1200×432 131 KB

Hi Guys! Hope all of you are fine.

Based on the attached table relationship, I can add row to the child table if I open ‘Form’ using/binding the parent table. But for some reason, I have a situation to update the parent table but the form will be bound to the Child table. That means at first I want to add a row to the child table and then update some info of the parent table.

Please help me to know if it can be done.

Thanks in Advance.

1 Like
2

shahappsheet:

But for some reason, I have a situation to update the parent table but the form will be bound to the Child table. That means at first I want to add a row to the child table and then update some info of the parent table.

This doesn’t make sense. Please try describing it in a different way. A screenshot or two might be helpful as well.

3

Thanks a lot Steve for you kind response.

I will come up more precisely tomorrow.

Regards.

1 Like
4

Hi @Steve! Very Good day.

3X_9_7_970a747ca26fb099f26768da08c4ac6c8b202f06.png
3X_9_7_970a747ca26fb099f26768da08c4ac6c8b202f06.png665×146 2.95 KB

My Query is that-

  • I will log into the ‘UserLog’ Table.
  • When a record is added into the UserLog table, the corresponding [LogInDateTime] for the specific User will be updated in the ‘UserInfo’ Table concurrently.

I can not do it here. I am new here. So, Please help me to find a way for this.

Regards.

1 Like
5

Create a new action to add a row to UserLog from a row of UserInfo:

  • For a record of this table: UserInfo
  • Do this: Data: add a new row to another table using values from this row
  • Table to add to: UserLog
  • Set these columns:
    • UserLogID: UNIQUEID() (I’m guessing?)
    • UserID: [UserID]
    • LogInDateTime: [LogInDateTime]
  • Prominence: Do not display

Configure the Form Saved event action for the UserInfo form view to use the action.

See also:

3 Likes
6

Thanks a lot @Steve.

I have done the way you suggested. Thanks Steve. But I need to do the way around. That means, I will add the log and update the UserInfo. Therefore, I mentioned before that " I will log into the ‘UserLog’ Table". So the form will be based on the UserLog (Child) Table not the UserInfo (Parent) Table. I hope that I could explain.

Please help me.

Thanks in advance.

1 Like
7

Try this:

Remove the config I suggested earlier.

Add a new normal (not virtual) column to UserInfo called (e.g.) LogIn of type Ref to UserLog.

Create a new action for the UserInfo table:

  • For a record of this table: UserInfo
  • Do this: Data: set the vaslues of some columns in this row
  • Set these columns:
    • LogIn: MAXROW("UserLog", "LogInDateTime", ([UserID] = [_THISROW].[UserID]))
    • LogInDateTime: [LogIn].[LogInDateTime]
  • Prominence: Do not display

Create a new action for the UserLog table:

  • For a record of this table: UserLog
  • Do this: Data: execute an action on a set of rows
  • Referenced Table: UserInfo
  • Referenced Rows: LIST([UserID])
  • Referenced Action: (action created above)
  • Prominence: Do not display

Configure the Form Saved event action for the UserLog form view to use this second action.

1 Like
8

Tomorrow, I will try and let you know.

thanks a lot.

1 Like
9

Hi Steve!

I works great with some modification. Actually the modification is very simple.
In the Referenced Rows of the UserLog Table Action I have used a FILTER expression and in the action of the UserInfo table, I have used simple set values parameters ‘LogInStatus=True’ and LogInDateTime=‘Now()’.

So, finish and Done.

Actually I couldn’t understand the use of the Referenced Rows. When I saw your intension of use in your script then I just modified and it Worked.

Great Steve.

Thanks a lot.

1 Like
10

Hi Steve! Hope you are doing well.

My purpose was served for my current particular situation. But till now I could not solve this issue- Add to the bound table and Update another table. Because the main limitations here that I cannot carry value from the bound table to the referred table where the update will be occurred. At least for far I have gone through.

In my current situation it was done as it was not required to carry values from the bound table to the referred/related table where update was done. AppSheet is too much single table oriented. Sometimes feeling suffocated.

Thanks.

1 Like
11

Hi @Steve!

At last I could do it. I think now I can say it can be done. But to get the value from the other table, Main bound table, is not easy. The Expression is as follows-

ANY(SELECT(Item_Price_Setting[ItemPrice],([ItemPriceSettingID]=MAXROW(“Item_Price_Setting”,“_RowNumber”,
([Item Info ID]=[_THISROW].[Item Info ID]))),TRUE))

If there was an option to pass the value to other table for updatation as it is in case of “Add value to other Table”, life would be so easy. For single user it is working but didn’t try for multiple user YET.

But even though if this way can work, in general, then no problem. Thanks a lot @Steve and AppSheet community to get this support.

1 Like