Hi All,
I have 2 tables TABLE1 and TABLE1_HISTORY. I need to combine latest 1 row from TABLE1 and few historical rows from TABLE1_HISTORY and display the values in a single grid.
Instead of using UNION to combine the 2 tables using DB views or querying the 2 tables separately and combine in Appian side , is there a way to union 2 tables using recordtypes ? relationship creation is not working as the PK / FK relationship doesn't exist.
Note Here: As per our current design , the common field between the 2 tables are not a PK field in either of the tables because of which i am not able to create relationship as join / union in SQL.
Appreciate the suggestions. Thanks in advance
Raj
Discussion posts and replies are publicly visible
Should be simple. You can create custom record fields in the primary record that reference field from the other one.
Hi Stefan ,
Thanks for the response. Are you suggesting have a customfield created which is fed by a queryRecordType on TableHistory table with filter on common field value ? Then when we want to display both records in same grid , i guess it may get complicated . Or i might missed to understand your response :-( sorry . Could you please provide any sample or link in appian forums if you have it handy .
Thanks in advance.
sorry we cannot use the queryRecordType in custom when checked, so how we make the connection without relationship or how we can use customField for relationship .
Thanks
When creating custom record fields, you can use customfielddefaultvalue() to add values from one record to another.
https://docs.appian.com/suite/help/23.4/fnc_crf_customfielddefaultvalue.html
When doing so, your final record will contain fields from both "tables" and can easily be used.
without relationships created , we will not be able to use from both tables in same customfield. When tried i got error. My issue is creating relationship between 2 tables on non pk's. Thanks for the response