How to Add Connected Record Value Automatically to Child Table

Hi Folks
Hoping someone can help me here. I am probably missing something obvious!!

My Table structure is shown below.

This works fine for data entry.

I am having trouble creating a report for a Project that includes data from the Table at the bottom of the table relationship, ie the PJ/BEH/RP/Details/FY/$Amount Table.

When I create a View Project details page, and try to add to the Page a connected view (Grid or Pivot Table) of the PJ/BEH/RP/Details/FY/$Amount Table, Knack does not let me see this Table and thus I can’t even choose this Table.

Not sure why this is the case as I can see the other Child Tables in the structure. It looks like there is some limit on the number of child table levels that can be used when reporting.

So to overcome this I tried adding a connected field to the Project Table and then manually adding the Project Number directly to the PJ/BEH/RP/Details/FY/$Amount Table.

Woohoo, problem solved, sort of.

I can now ad a view based on the PJ/BEH/RP/Details/FY/$Amount Table.

But, having to manually add the Project number to every record in the PJ/BEH/RP/Details/FY/$Amount Table defeats the purpose of a relational DB.

Given that PJ number is a connected record, albeit through several levels, I figured there must be a way to automatically add the PJ number as a connected record

I have tried using
a) a Text formula to add the PJ number, which adds the number, but Knack does not recognise this as a Connected field, which makes sense
b) a Conditional Rule where the set to a “field Value” option is chosen, and Knack lets me choose “Project Table” in the dropdown box. Looks very promising but when I add a new record in the PJ/BEH/RP/Details/FY/$Amount Table, nothing happens.
(BTW there is virtually no documentation on how the Conditional Rule where the set to a “field Value” option works, seems to be a well kept secret)
c) I also tried Record rules but no luck there either.

So a long explanation to what should be a basic Knack functionality!

Have you tried using the Record Rules on the input page? If you select to update the PJ/BEH/RP/Details/FY/$Amount record on submission you should be able to update the PJ field from the connected PJ/BEH/RP/Details record (assuming that has the PJ record).

1 Like

Hi! I’m wondering if you found an answer to this? I have a table being populated by an API and I want to automatically set connected records based on values in the table. I can’t use form submission rules etc because the data isn’t being entered by form. Knack have said this would require customisation so wondering if anyone has developed similar already?

1 Like