Help: Create table in html and reference values ​​to child table by list

[nghilttran]

I have a data structure as shown. In the Info column I want to create a table that gets values ​​by Products_Name referencing the ANS5_P1_9 table. Here is the body of the formula:

<tbody>
<<Start: ORDERBY(SELECT(ANS5_SUB_P1_9[Products_Name], [Products_Name] = [_THISROW].[Products_Name]), [No])>>

<tr>
<td><<[No]>></td>
<td><<[Certificate]>></td>
<td><<[Cert_Authority]>></td>
<td><<[Expiry_Date]>></td>
</tr>
The result is displayed:

<<start:>>

<<end>>

<<[No]>> <<[Certificate]>> <<[Cert_Authority]>> <<[Expiry_Date]>>

Could not get value from REFERENCE TABLE

[scott192 · Accepted solution]

I'm sorry to tell you that your end goal to construct a table within a LongText field is not possible.  You can construct static tables, but not changeable row ones.  You are also using template expressions that can only be used within templates.

Looking at your data structure, I am not sure whether you understand parent child relationships?  Your table RECORD has an ID column, but you have set the Product_Name field as the KEY rather than the ID.  Your table ANS5_p1_9 does not seem to have a KEY column unless that is being deliberately hidden.

If your data structure is set up correctly, Appsheet will construct an Inline table automatically that shows the child records when you view the parent record.  You can of course use an orderby(select(...)) expression instead of a reference expression should you wish to filter the records returned.

I am sorry if I have somehow misunderstood your intent or knowledge of Appsheet data relationships.  Happy to help correct it though if I've surmised correctly.

[nghilttran]

I need your help. Please

[scott192 · Accepted solution]

I'm sorry to tell you that your end goal to construct a table within a LongText field is not possible.  You can construct static tables, but not changeable row ones.  You are also using template expressions that can only be used within templates.

Looking at your data structure, I am not sure whether you understand parent child relationships?  Your table RECORD has an ID column, but you have set the Product_Name field as the KEY rather than the ID.  Your table ANS5_p1_9 does not seem to have a KEY column unless that is being deliberately hidden.

If your data structure is set up correctly, Appsheet will construct an Inline table automatically that shows the child records when you view the parent record.  You can of course use an orderby(select(...)) expression instead of a reference expression should you wish to filter the records returned.

I am sorry if I have somehow misunderstood your intent or knowledge of Appsheet data relationships.  Happy to help correct it though if I've surmised correctly.

[nghilttran]

Sorry for my wrong information. The ID column in the table is redundant. Both tables RECORD and ANS5_P1_9 choose Products_Name column as key. I wrote the formula as below but it is not showing the expected result. Please help me

<table>
<tr>
<th>No</th>
<th>Certificate Information</th>
<th>Authority</th>
<th>Expiry day</th>
</tr>
<<Start: ORDERBY(SELECT(ANS5_P1_9[Products_Name], [Products_Name] = [_THISROW].[Products_Name]), [No])>>
<tr>
<td><<[No]>></td>
<td><<[Certificate]>></td>
<td><<[Cert_Authority]>></td>
<td><<[Expiry_Date]>></td>
</tr>
<<End>>
</table>"

[scott192 · Accepted solution]

It is not possible for you to have the ASN5_P1_9 Products_Name column to be set as the KEY because you can clearly see that the column has repeated values.  KEY column values must be unique to satisfy the constraint.  Appsheet surely must have been complaining to you that there are duplicate key values in your table?

Add a new column in your ANS table called ID.  Regenerate your schema in Appsheet for the ANS table so that your new ID column appears.  Remove the KEY tick from your Product_Name column and put this tick on your ID column instead.  Set the Initial Value of your new ID field to be UNIQUEID()

Now, choose your Product_Name field again in your ANS table and set it to be a Ref type.  It will be a Ref to your RECORD table so make thechanges and save.

If you examin your RECORD table in Appsheet, you should see a new field has appeared at the bottom called Related ANS_P1_9s.

Now that is all working, you should examine your Start: expression.  The Start expression MUST return a list of KEY field values otherwise you'll get all sorts of errors.

Change your Start expression to 
<<Start: ORDERBY(SELECT(ANS5_P1_9[ID], [Products_Name] = [_THISROW].[Products_Name]), [No])>>

That will return the correct records...but!  Your expression will still not work as it is meant to be set within a template...it will NOT work within a LongText field

[nghilttran]

I will add ID column as key. Products_Name column of ANS5_P1_9 table ref to RECORD table. So how to write formula?