Solved: Reference a Dereference - Page 2

JpChapron · 08-27-2022 07:10 AM

Hi

So it might sounds silly, but I'm trying to add info from a parent to a grandchild's reference when we create a new ref For the grandchild... from the child! (I'll add pictures)

The Parent contains a field called [Client ID]

The Child Contains the same [Client ID] as a dereference

The Grandchild also contains the [Client ID] this time as a reference.

Time for photos ! (Edit : I mixed up 2-3, 3 is 2 and 2 is 3)

1

2

3

4

5

So we need to pre-populate the last one (5) with information coming from the child, but only when creating a NEW one from this formview (when it's a grandchild). Keep in mind that this is a REF

I've tried to create a De-Reference to the Parent, name [Ref to parent] then add this formula to the row : [Ref to Parent].[Client ID]

but the result is not the expected Ref-type.

I'm really scratching my head to know if it's even possible... !

WillowMobileSys

Got it! To re-state...

When using the "New" function on a dropdown pop-up list, the prominent Ref column is not pre-filled like it is when you have a Parent/Child relationship established. (Note: The usage of client in your use case is not a Parent/Child. It is simply, information by reference).

This has been a long standing problem and I believe there is a Feature Idea for this. I am not completely convinced that we developers would always want it to be pre-filled (depending on circumstances) but I know I wish it was frequently.

In the meantime, you will need to find an alternative. One option is to NOT show or require the Client ID when you first insert it. Then auto-fill it with an action on Form Save.

View solution in original post

WillowMobileSys

When you open the "main" form and then tap 'New" for adding additional details - this include child rows as well as new dropdown choices - everything runs under the context of this "main" or "Host" form. Nothing is actually Saved until the "Host" form is saved. The point being that any Form Saved behavioral actions on the "Subordinate" Forms are simply ignored.

So, you will need to attach your new action to the Form Save behavior of that "Host" Form.

Using "Host" and "Subordinate" to prevent confusion with Parent/Child!!

View solution in original post

WillowMobileSys

There are a number of options handle the passing of the value

1) Try to see if you can use the INPUT() function. AppSheet states it is still considered Beta but many are using it in their live apps. This will be the permanent solution if it ever gets finalized.

2) Create your own "parameter" table to store and retrieve the value you wish to pass. This is what I typically do...when I can't use INPUT().

3) You could make some strong assumptions and use a SELECT() expression to just blindly select and assign the value. For instance if you were to record a Edit Date updated each time a row was added/edited, you could assume that the row with the latest Edit Date is the one to pull the value from. When you get to the action that performs the actual update, use an expression that pulls the Client from the row with the latest Edit Date. This works because it is all happening on the device side and there is no concern of other users sneaking in an update.

4) You could take advantage of a less-than-ideal quirk in the "add a row to another table using values from this row" type action. If you implement this action as if you were adding a new row BUT also specify the specific row key of the row just added, it will behave like an "UPSERT" function - inserting the row if not present or updating the row if already present - which should be the case for you. The catch here is that you MUST specify EVERY editable column in the action. Assign the new value for columns to be updated. For those columns you don't intend to actually update you will need to re-assign its current value which likely requires a SELECT() expression to retrieve it's current value from the table. Like I said it is quirky but it works!!

View solution in original post