0

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?

9 replies

null
    • Ninox Software
    • David_G
    • 4 yrs ago
    • Reported - view

    There are two different searches, one of them is a deep search that looks at fields in the tables, but not data generated views. The other is a search within a current table. Have you tried using both searches?

    • Halio
    • Halio
    • 4 yrs ago
    • Reported - view

    I'm talking about the search that I get when I click on the field record link to at a record from the other table. I am not aware of two searches for that.

    • Halio
    • Halio
    • 4 yrs ago
    • Reported - view

    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. 

    • Jorg
    • 4 yrs ago
    • Reported - view

    Hi, 

    The search in the table that popps up in a reference field can only find data that are stored in the very table that is to be connected. 

    Kind regards, Jörg

    • Halio
    • Halio
    • 4 yrs ago
    • Reported - view

    Jörg, it would be very helpful to be able to search function fields as well and if not by default then as an option. Most of the time data is pulled in to many tables, for example student and guardian names, if we cannot search by them, we cannot find the relevant data.

    • Venture to Market LLC
    • Jay_Holman
    • 2 yrs ago
    • Reported - view

    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?

    • Alain_Fontaine
    • 2 yrs ago
    • Reported - view

    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)
    end

    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.

    • Robert_Larkin
    • 2 yrs ago
    • Reported - view

    "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

    • Fred
    • 2 yrs ago
    • Reported - view

    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.

Content aside

  • 2 yrs agoLast active
  • 9Replies
  • 1998Views