Searching in a n:1:n table not working
Searching for a record in linked n:1:n table doesn't seem to be working for me.
I have a table with a list of guardians, another with students, and another that links the two to show their family structure. Then we have another table from which I want to link to the family structure but when I try to search for any guardian's name or students' name, I get no results. Their names are concatonated and shown in two columns, is that why it doesn't work? If so, how can I fix this?
Having to manually select a record is immensely inefficient.
To connect our students with all guardians, I have a family structure table. It looks like:
students -> family structure <- guardians
Then in many other tables we need to refer to the family structure like:
payments -> family structure
I concatonated the guardian names and the student names as the main reference column. Now in this case it is still okay to manually select the correct family but in other n:1:n structures where there are many more entries, I find it impossible to link to the correct record without being able to search.
i hope I'm just doing some silly mistake and there is some simple solution to this.
Has there been any progress on this?
Jorg indicated "The search in the table that pops up in a reference field can only find data that are stored in the very table that is to be connected." I tried creating a formula in the "table that is to be connected" that brings in data from another table so that I could search on that formula field, but that doesn't work either. Are there any workarounds?
Not exactly what you are asking, but maybe a way to make the problem manageable.
In the "family structure" table, create a button, named "Add payment", with the script:
let lastpay := last(select Payments);
if lastpay.'Family structure' = null then
let me := this;
lastpay.('Family structure' := me)
To use this:
1- create a new payment;
2- go to either the "students" or the "guardians" table;
3- search for the relevant student or guardian;
4- tap the "family structure" link;
5- tap the "Add payment" button.
"I tried creating a formula in the "table that is to be connected" that brings in data from another table so that I could search on that formula field, but that doesn't work either. Are there any workarounds?"
incredibly frustrating that this doesn't work as expected.
i have two tables. 'catalogue' which is a table with mostly static data on a catalogue of items. and 'inventory' which links to 'items' n:1 and has each row representing an object in my inventory corresponding to the catalogue of items. i track purchase price, sale prices, condition, etc on a per-item basis so this arrangement is quite inflexible.
i have another table 'sales' where each row corresponds to one sale, often of multiple items. because the easily searchable fields are stored in 'catalogue' and called into 'inventory' making links from 'sales' to 'inventory' is tedious and error prone and even after using the search bar to narrow down by fields in 'inventory' involves some hefty scrolling (huge inventory) which is the kind of repetitive physical motion i turned to ninox to minimise.
no idea where this person is trying to point me - https://ninox.com/en/forum/technical-help-5ab8fe445fe2b42b7dd39ee7/is-it-possible-to-search-via-a-reference-field-and-then-quick-select-a-result-via-a-shortcut-5db282b597213028dfb9571c
i've taken ninox's quirks and limitations in my stride so far but this one is getting me doiwn
Hi Robert -
To clarify for myself, your catalogue table stores all items that you could have in your store.
Your inventory table has a reference field to catalogue so you can list the items you actually have in your store to sell.
Then you have a sales table that tracks items sold to customers. One sales record can have multiple items from your inventory table. You did not mention of a N:N table that links sales and inventory. Something like 'saleItems", where you can link a sales record to multiple inventory records.
So you would create a new sales record, then click on add record of the saleItems view that would be created when you create the new N:N table. Then you would click on add record for the Inventory view that is on the saleItems record. You can then type in the name of the item in your inventory and you wouldn't have to scroll as much. Or you can use your Inventory Numbering or anything else you track in your inventory table.
Let us know if that helps.