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?
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
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.
Thanks for the response , when you make the common field in TABLE 1 as unique , the same field is not unique in TableHistory . here the relation ship must 1 to many right. Might be i need to rethink of redisgn the table.
Thanks again