Hi, I want to show values from a connected record. I have 4 key tables:
Organisation: Organisation name, address etc
Project: Lead organisation (connected field to the Organisation table above), goals, start date etc.
Quarterly report: Project (connected field to the Project table above) date, quarterly reporting
Case study: Quarterly report (connected field to the Quarterly report table above), case study, name of person sharing etc
What I want to do is pull the name of the Lead organisation from the Project table onto the Case study table in a table on a page view but I can’t add the column as it only allows one connection away. So then I thought to do a text formula bringing the organisation name onto the Quarterly report table from the Project table but the field doesn’t display as a field I can pick for my text formula.
Can anyone give me a hand? It feels like such a basic thing to be able to do. I work with Airtable too and there I can just display a look up field from a connected record. I know it must be possible in knack but can’t figure it out!
Hi Kimberly, thanks for your help. Yes, I’ve connected Case studies but to the quarterly report rather than the organisation directly. So I have a grandparent (Project) a parent (Quarterly report) and a child (Case study) and I want to show the grandparent on the child. I don’t think it’s possible in Knack but I wondered if there was a clever workaround anyone has come up with?
Thank you for posting your question! You are correct, this isn’t possible within Knack. With cascading connections in Knack, it will only recognize a “great grandchild” table through the “grandchild” table relationship. Tables with deeper connections are not available for selection in Knack. In the “Connections” section, you can add and remove fields from any connected tables which are connected to the primary table of the record being displayed.
You can add a new connection field in the Case Study table as Kimberly suggested to accomplish your goal.
Jumping in on this one as I think the Text formula is the right way to go. Although I appreciate you’ve not been able to get it to work. You should be able to pull through fields from the Projects table to the Quarterly Reports table using this method.
In my experience, sometimes the syntax of the field name isn’t immediately obvious when doing the text formula. I often prefix the field name with something very obvious and unique like:
“YYYY Lead Organisation”
Also, in case you haven’t already done this, type the field name in the black text editor window starting with the usual curly bracket, so you’ll have {YYYY, this should show you the field as a lookup after you type the four Y’s
If you have already typed it and this was painfully obvious….please accept my apology
There shouldn’t be any reason that the field won’t show up. Obviously, I may be missing something.
I have many applications where I’ve used text formulas to pull through records from the table above, so I can use them in the grand child table.
@CarlHolmes Thank you for sharing this info and helping out! I have learned so much over the past year and half working at Knack reading your solutions in our community forum!
I recreated the tables Alice shared above in a test app to try this out with a text formula earlier before replying to this question, however, the Organisation connection field in the Project table is not available to add into the text editor of the text formula since connection fields in another table are not able to be used.
As you said before, I may also be missing something! Or possibly, I’m misunderstanding how Alice’s app is setup.
Hi @Les - I appreciate your kind words and am pleased that my contribution has been helpful.
I feel that there is still a workaround to this issue using Text Formulas. I now understand that the field you are trying to pull through is actually a connected field, which makes sense.
You could try using a text formula to “convert” the Organisation to text in the parent table. I’d simply create a new field (“Organisation Text Only”) next to the Organisation field so you have a text version of it to then pull through to the table below.
You should then be able to use another text formula in the child table to lookup this converted “Organisation Text Only” value.
One downside to this is that the Organisation will only be a text value, but this could be enough if you just want to show it in a grandchild details view or table.
As it will only be text, there will be some limitations to fields functionality. Such as, you won’t be able to link to an Organisation page or show Organisations in multi choice drop-downs in search components or filters.
The other way around this to setup a connected value in the child table to the Organisation so it is set when the parent record is created. This will also need to have a record rule if the parent is updated to updated connected values.
