Hi,
I have a need to setup a history of interactions with a job file. I currently have it working fine where when someone adds a note a new history entry is created in a History table, when someone enters a new order for the job a history record is created in the same History table.
The problem is that the records that are being created are not connecting to the job directly. So I create a view with a table to show all history for that job and I can't. All I can do is individual tables showing "records further connected" to the job for each history type (i.e. order or note).
Is there any way to get these history records directly connected to the job or to make a table view that will show data from multiple sources?
Okay, as you've found Knack allows only one object per table or list so what we've done here is to create a new Notes record for each Order created or updated and linked to the parent Job of the Order, and in the Notes table include a link back to the Order as well. You can do the same for the History object too.
It means connections can multiply in number quickly though.
I have a table that holds my 'Notes', it is connected to my 'Jobs' table as a child, all new Notes are correctly joined to the parent. It is also connected to my 'History' table with the history table and is joined correctly. When I want to view all notes joined to a specific job this works. The issue is when I want to see the 'Notes' as well as 'Orders' in the same table.
I have a record rule that runs on the form submission to create the history entry which works fine. However when I try to set the 'Job' connection field in my 'History' table to the same value as the job connection field of the note it gives me the error "No eligible fields exist".
We do this by finding a common connection amongst the tables (like a company, or a contact and in your case the Job) and then making sure the Notes object is connected properly.
Then make sure the Job is set correctly in the Record Rules of any form that adds a Note.