I have 3 records having one to many relationship Timesheet(ID,IS_ACTIVE), Audit(ID,TIMESHEET_ID,IS_ACTIVE) and Comments(ID,AUDIT_ID,TIMESHEET_ID). When I tried to use single write records smart service, I am not able to populate value for TIMESHEET_ID column value in "Comments" table. Is it expected or do we have any other way to handle this?
Discussion posts and replies are publicly visible
You mean to say you can bale to populate timesheet id in Audit but not able to populate in Comments.
Yes correct. In write records, the record input is "Timesheet". Record structure will be like below
Timesheet(
ID:1,
IS_ACTIVE:1
AUDIT*(
AUDIT(
TIMESHEET_ID:1,
COMMENTS*(
COMMENTS(
TIMESHEET_ID:null,
AUDIT_ID:1
)))))
When writing the main record plus related records to the DB, Appian will take care of the foreign keys, when you configure your relationships correctly.
Hi Stefan Helzle . Yes I agree. As mentioned above, timesheetID column in Audit table is populated as expected. Only issue was with timesheetID column in Comment table.
Maybe I can show you in different way which I tested. Please check below
InputAfter write record SS
and relationship is configured properly
I do not know whether Appian will populate foreign keys across two levels of relationship.
Peter Lewis, can you shed some light on this?
No it won't populate those foreign keys automatically. Can you give some context though - why do you need to have that foreign key in the comments table though? You should always be able to go two steps through the relationship to get from Comment > Audit > Timesheet.
I thought of adding timesheet id column in comment table so that I dont need to query the audit table to get all comments associated with that timesheet.
You can still do that manually.
In my opinion, "lookup-style" queries like this where you want to get the parent ID (even down multiple levels) are pretty performant in Appian, so it just isn't worth the extra development work to denormalize your schema.
If anything, this data model likely becomes more confusing to deal with since now there are 2 potential paths to get the timesheetId, and the performance difference is minimal.