Question

Combining 2 similar recordtypes

Forum|Forum|2 years ago
January 9, 2024
13 replies
0 views

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

M

mathurambm639546

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?

R

rajeshr6085Author

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

Raj

M

mathurambm639546

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.

stefanhelzle0001

Brainy

Should be simple. You can create custom record fields in the primary record that reference field from the other one.

R

rajeshr6085Author

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.

Raj

R

rajeshr6085Author

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

W

waden3810

[deleted]

stefanhelzle0001

Brainy

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.

mathieud0001

Brainy

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.

R

rajeshr6085Author

yes it can work, but it is convoluted.

Sign up

Login with SSO

Login to the community

Login with SSO