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!
