My aim is to be able to click on a Retailer in a grid table and have a pull up a new grid which shows the name of the retailer and then all the locations.
Maybe I’m missing something here but this is the purpose of a connection in Knack. You would set up a 1 to many connection between retailer and location. Load the retailers first. Then load the locations. The connection will automatically make the “join” for you.
But only if the connection is defined before you load the data. You may need to export and reimport those tables to create the connection. There is no way to do so if the data is already in place that I am aware of.
Clearing the table, making the connection and reloading the data is not a problem. However, all I can see is how to make connections between tables and not the fields in the tables. If I just make a connection between the tables, how does Knack know that I want the retailerid in retailers linked to retailerid in locations?
Juts to be clear, a connection IS A FIELD - in this case a field in the LOCATIONS table, and it defines the “join” between the tables.
You make the connection in the Locations Table. The connection field name is really up to you - but best to keep it the same as the matching field in the other table so its obvious. Define it as:
Each Location connects to one Retailer
Each Retailer connects to many Locations
The Retailer Table display field is what you will be matching on. So if you set the Retailer Table “display field” to be the Retailer ID, then you will need the matching retailer ID in that field when you import the Locations Table. If you set it to the Retailer (Name) then that’s what needs to be in that field in the CSV that you import. The choice is your as to which Retailer field “relates” the tables. You can change it later, once imported, if you want.
Note I added a SUM field as well in the Retailer table so it adds up the sales across the locations (just to show how that works).
If you would like to see how to do this, DM me your email address and I’ll set you up as a shared builder on this sandbox app so you can see how the tables and forms are built.
Basically, connections takes all the thinking out of linking tables. It has limitations, but does what you need here. Hope that helps.
Ok, thanks very much for that explanation and example.
So, a couple more questions.
Does Knack offer drop down boxes based on field contents that will then trigger a filter?
So to carry on from the example above, if I had hundreds of retailers, could I have a dropdown box that allowed me to select the retailer rather than having to scroll or type in the name in the filter box?
Does Knack have widgets that I can add to a page? So again, in the Retailer Details view, can I have a large box to the right which shows the Total Sales like this -
As said @LeighBerre96926 a connection is a Field that link 2 Tables.
When you create a Table, in Table Settings you can also choose which Field to display to represent the whole Table.
Yes to both of your questions.
The first one (filter) has been answered in the Knack Forum.
The segund one, is done with CSS code targeting a Detail view and fields.
Not widgets a such but there are simple methods to rearrange fields, highlight fields, etc without code or extra CSS. Explore those first. Then if required use JavaScript or css to customise.
So this example is without doing anything that isn’t “out of the box”. It just uses another detail form, using standard formatting options. If you want it more colorful etc, CSS will do it.
The other feature that you might like is that the details page can be shown in a modal popup rather than a whole new screen, as now set up in the example app I provided a link to above. Some like modals, some dont …
Indeed. And a middle of the road between in-the-box tools and CSS in the API, is CSS like text size and color inside a field Title. Knack has indeed a lot of flexibility.
not sure if this is going to be useful for you, but it may be. I have something similar set up in my hotel directory listing Hotel Groups > Hotel brands > individual hotels. Have a look https://gtrn.knack.com/gtrn3#hospitality-groups/ and for example find “Wyndham Hotels” then select any of the brands that you see under the Group name. The brands in turn show all the hotels within that brand, but crucially that part is not a grid but a Search, allowing me to add filters to make a long list easily searchable on the basis of the variables in the “Hotels” dataset. Eg you can search for Dolce Hotels in Europe. All hotels are linked to their brand and from there to the mother group - so basically 3 layers, not just 2.
