Data Entry in two tables at a time


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.

:pray: Please help me to know if it can be done.

Thanks in Advance.

1 Like

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

Thanks a lot Steve for you kind response.

I will come up more precisely tomorrow.

Regards.

1 Like

Hi @Steve! Very Good day.

image

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

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:

2 Likes

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. :grin: :pray:

Please help me.

Thanks in advance.

1 Like

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

Tomorrow, I will try and let you know.

thanks a lot.

1 Like

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.

:pray: :pray: :pray: :pray: :pray: :pray: :pray: :pray:

1 Like

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

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. :grinning: 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