Reference a Dereference

[JpChapron]

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 · Accepted solution]

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.

[WillowMobileSys · Accepted solution]

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!!

[MultiTech]

Check out "chaining dereferences"

Let's say we had a system to send orders to customers, and there was a problem during the packing of their order and we need to get in touch with them.  Let's say we had the following hierarchy:

1. Customer
2. Order
3. OrderDetail
4. PackingOrder

And let's say we've got a button on the "PackingOrder" level, for our workers to press if there's an issue and they need to send an email to their boss - containing the contact info of the package owner.

From the PackingOrder level:

[PackingOrder_Detail_Link].[OrderDetail_Order_Link].[Order_Customer_Link].[Customer_Contact_Info]

• From the PackingOrder record, go into the OrderDetail this is linked to
• From that OrderDetail record, go to the Order it's linked to
• From that Order, go to the Customer it's linked to
• From that Customer, grab the contact info column

[WillowMobileSys]

You can do multiple dereferences and if you have the REF's correct you can walk all the way back up from the grandchild.  

If you were to label them something like Parent->Child->Grandchild then you would have an expression like this:

Grandchild column expression = [Child].[Parent].[Client Column]

If I understand correctly...its that easy!

[JpChapron]

Thanks for your info,

My problem is this however, the child doesn't have a column referencing my child and/or parent.

That's because it's a Client name, and I don't think building the database and creating a NEW client for EVERY Report works, so the Report has a collumn with the Client ID, and the grand child also has this column so that all grandchild report back to the Client ID. This way every entry with this Client also has the Grand Child's history (in this case, it's passengers name)

So I don't know how to dereference something that... well has no reference !

[WillowMobileSys]

---

@JpChapron wrote:

My problem is this however, the child doesn't have a column referencing my child and/or parent.

---

Technically then it's not a child.  

I think it would be best if you were shown screenshots of the tables involved in your problem statement above.

[JpChapron]

Sure, the tables for the example in the main question are multiple, and maybe very complicated to show here,

I'll give you the same problem I have with Emails for Customers 

Table RDV_Info is the main information:

RDV ID

RDV_Prefix

RDV_Num

RDV_Date

RDV_Base

RDV_Pilote

RDV_Ame

RDV_Immat

RDV_Type

RDV_Client

RDV_Fuel_start

RDV_Fuel_end

RDV_Total Time

RDV_RevNRevNF

RDV_NF_Raison

RDV_NF_Remarque

RDV_Status

RDV_Signataire

RDV_SignataireEmail

RDV_Signature

RDV_CreationDate

RDV_CreatedBy

RDV number

RDV_Fuel_CIE_Bulk

RDV_Fuel_CIE_Drum

RDV_Fuel_Client_Bulk

RDV_Fuel_Client_Drum

RDV_Recalculate

RDV_AffretementHQ

RDV_EmailSentNumber

RDV_Extra1

RDV_Extra2

RDV_Extra3

RDV_Extra4

RDV_Extra5

Table Client is where the clients list is stored :

Client_ID

Client_Nom

Client_Adresse

Client_Postal

Client_Tel

Client_Contrat

Client_Affret

Client_NomSign

Client_Sign

Client_Email

Client_Extra1

Client_Extra2

Client_Extra3

Client_Extra4

Finally, Table ClientEmail is where the emails are stored

ClientEmail_ID

Client_ID

Client_EmailList

ClientEmail_extra1

ClientEmail_extra2

ClientEmail_extra3

ClientEmail_extra4

Maybe I should create a virtual collumn with the ID from the other tables ?

[WillowMobileSys]

I am not following.  So let me try to explain how the structure should be set up

Grandparent

G_ID	Name	<<Other Columns>>

Parent

P_ID	Grandparent (Ref)	Name	<<Other Columns>>
	G_ID

Child1

C1_ID	Parent (Ref)	Name1	<<Other Columns>>
	P_ID

Child2

C2_ID	Parent (Ref)	Name2	<<Other Columns>>
	P_ID

The idea is each "parent level" table can have multiple children - one-to-many - so they can't have explicit columns pointing to an unknown number of children. 

 Instead each "child level" table has a column that points back to its parent.  Each child row can have only 1 parent.  A parent table can have multiple sets of children.

Also, note that if the Ref columns are setup properly, each Parent table will automatically get a virtual column of the list of its children.  In the case of "Parent" above, it would have two virtual columns.

In the example tables above, If I was on a Child1 table row and wanted get access to some Grandparent info, I can use dereferencing to walk back up the tree like this:

[_THISROW].[Parent].[Grandparent].[Name]

[_THISROW] refers to the  Child1 row itself.

I hope this helps!

[JpChapron]

Thanks ! i'll keep on working on my dataset and try to make it work with your info

[JpChapron]

Hello Wilow, the more I think about all of this, the more i think, I'm presenting it wrong.

Let me present the data :

I have a report, in this report there is multiple collumns, the ones that matters are the "RDV_Client" and "RDV_SignataireEmail"

"Rdv_Client" collumn is of the REF type. and refers to a table that contains a list of all the clients, this table contains the key which is "Client_ID"

1- Inside the Form, "RDV_Client"

"RDV_SignataireEmail" is an ENUMLIST of the REF Type, and refers to a table that contains a list of all emails that are linked to a specific client, this table also contains the "Client_ID" key has a reference to the clients table

Inside the form "RDV_SignataireEmail"

So far, so good ?

Now, I can easily select my emails in the "RDV_SignataireEmail" collumn, and make sure this is based on the "RDV_Client" collumn, so that it's filtered properly. since the underlaying table for "RDV_SignataireEmail" contains the CLIENT_ID information

My Problem is this :

In the use case that I have a new Email to add to this "RDV_SignataireEmail" I want this to have the collumn "Client_ID" prefilled with "RDV_Client" 

"RDV_SignataireEmail" EnumList selection, with the option to add a new

I Can easily do that by Checking the "Allow Other Values" and clicking on the "New" at the top of the "EnumList" window, but the Client_ID is NOT prefilled

In the "RDV_SignataireEmail" "NEW", the CLIENT ID is blank, and i don't know how to refer to the "RDV_Client" from here to prefill it

[WillowMobileSys · Accepted solution]

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.

[JpChapron]

Ok well thanks for the info ! i didn't know it would involve actions, 

Now Follow up question : Where would I implement the Action ? I've just made a TEST action on the EmailList Table, and added it to the EmailList_Form view Action when it's saved, but nope, should I implement the action on the "parent not parent" table too?

[JpChapron]

Forget it, it IS what I have to do I just was faster on writing the answer than actually testing the thing lol, 

For my future self and others :

You need to create an action on the Table you want, and a Referenced action linked to the SAVE button on the Form view of the "Parent" or main table.

[WillowMobileSys · Accepted solution]

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!!

[JpChapron]

Oops ! Me again... so I found out that with the action, I can use a reference action to trigger data on the table of the "Subordinate" but.. I still need to "Pass" the "Client" row information from the "Host"... So I'm still lost has to how to do that....

[JpChapron]

Alright ! So I've built this expression, which ALMOST Works but doesnt...

LOOKUP(list([Client_EmailList]), "RDV_Info", "RDV_SignataireEmail", "RDV_Client")

The first element of "LOOKUP" was asking for a list, so I just wrapped [Client_Emaillist] in a list() expression

However, if [RDV_SignataireEmail] has more than 1 email selected (Remember it's an enumlist), it cant check itself against the [Client_EmailList] as it will not be Equal, since it is simply 1 value

Still searching....

[WillowMobileSys · Accepted solution]

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!!

[JpChapron]

Thanks for the infos !! I'm working on that right now, I'm curious about the INPUT() function, do you have any examples of the function being used like you mention (To pass data from another table to a new one without user interaction) So far, I can only see examples of the INPUT being used to modify data from a Detail view without having to switch to the Form View

EDIT : Forget the question it's very straightforward and easy to do, I do have a more complicated use case however that i'll detail in another post