Hello!
I would like to share my approach to a multiple column primary key table and see if this is considered a best practice or if there is a better way to do this.
My data is stored in a Cloud SQL PostgreSQL instance.
TABLE A
- Column1 (PK in the source database, ref to another table)
- Column2 (PK in the source database, text field)
- other columns...
- Column3 (Virtual column created by AppSheet as the PK for this table)
- formula: CONCATENATE([Column1],": ",[Column2])
TABLE B (includes a FK reference to TABLE A)
- Column4 (PK of this table)
- Column5 (Text value)
- VALID_IF: SORT(TableA[Column1])
- Note: I use SORT so the values are displayed in alphabetic order in the dropdown
- Column6 (Text Value)
- VALID_IF: IN([_THIS],SELECT(TableA[Column2],[_THISROW].[Column5]=[Column1]))
- Note: I do this to filter the dropdown to dependent values only. AppSheet would automatically filter the dropdown, however, when I apply the SORT on Column5 above, it no longer filters this dropdown so I need to use this formula.
- Note: I do not know how to apply the SORT or ORDERBY to this syntax.
- other columns...
- Column7 (Virtual Column I created as the FK reference to Column3 in TABLE A above)
- Column Formula: CONCATENATE([Column5],": ",[Column6])
I feel like the simpler and more performant approach would be to change my source database by using a surrogate key instead of a multiple column key, but I wanted to give this a try to see what is possible in AppSheet.
Thanks!
LinkedIn
Twitter
