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
Hi Raj,
When you say TABLE1_HISTORY, does it contain the audit details of TABLE1, or are we storing all updates to TABLE1 in TABLE1_HISTORY?
Should be simple. You can create custom record fields in the primary record that reference field from the other one.
Spammy spam, oh so sly,Filling inboxes, oh my, my.Clickbait words and tricks they play,Trying to lead us all astray.
Spammy spam, we’ll delete you,No more tricks, we see right through.Unwanted emails, be gone far,Spammers, you won’t get too far.
Spammy spam, just a bother,Trying to sell what we don’t want, brother.We’ll report you, make you flee,No more spamming, let us be free.
Spammy spam, it’s no game,We won’t fall for your false claim.Respect our inbox, hear our plea,Spammers, now it's time to flee.
Hi Mathurama,
Thanks for the response . Yes , it kind of audit . But the history cannot be pulled using the relationship ,as including the PK from table 1 is moved to history . The relationship / connection between these tables are is on a common field , not as a PK or FK relationship.
Table 1 stores only 1 latest record for a particular workitem . all history is stored in Table1History and only during the grid creation i need to pull combined from both recordtype . I will be able to combine using a generic VO object , but trying to understand any way using recordtype in built functionalities.
Thanks
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 .
In the first place, I don't understand why there is no FK/PF relationship between these tables, given that the latest record in TABLE1 is going to have all its history in the TABLE1_HISTORY table.However, if there is a common field between these tables, and if it is going to be a unique field in the TABLE1, then you can make that column as UNIQUE in the database. Eventually, this configuration will allow you to pick up the common field in the Records when configuring the record relationships.
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.
Only solution to this issue is somewhat convoluted. You could create a 2 Web APIs that query the same fields on both tables. Create a single Service Backed Synced Record Type that combines the results of the 2 Web APIs.