Hi all, newbie to Knack here and have enjoyed experimenting with it today - but would really appreciate input from those more experienced on whether I’m tackling this the right way…
Table of projects, each will have an estimated completion date, updated over time to reflect any slippage, etc.
I need an audit history of each time this changes, and why.
I’ve created a connected table to track updates, with fields for estimated completion date, reason for change, date changed, changed by.
My instinct (and this might be because I’m thinking code rather than no-code!) is that a new record would just be added to this connected table each time the estimated completion date is changed. But…
-
I can’t work out how to only display the estimated completion date from the most recent record in the connected table when displaying a grid of projects, it displays the dates from all connected records.
-
I can’t get an action link in the projects grid to allow the user to create a new estimated completion date record for that project that would allow them to input the new date/reason via a form.
- I can add a button to a form on the same page, but then the project has to be selected on the form.
I have managed to get this working by also adding the estimated completion date and reason for change fields to the project table, then using an update button in the grid to show a form for updating these two fields in the projects table.
Rules on this form then create a record in the connected table which gives me the audit log history of the change.
Have I missed anything, or is what I’ve done the right way to tackle something like this in Knack? Having the data of the latest estimated completion date and reason duplicated in both tables feels ‘wrong’ when I think back to database design principles I was taught many years ago, but that may just be that I need to shift mindset a bit for a no-code world! 
