1

Unable to populate related table in Choice (dynamic) field

I have two tables: Category and Subcategory, each with single Name text fields. Here's the data model:

relationship

I'm using these tables to populate choice (dynamic) fields in another table named Entry. I use the following to populate a "category" field in the Entry table:

(select Category) order by Name

and I select the Name field as the dynamic value. That works great. Now I want to select on the related subcategories for that category. I use the following to populate the "subcategory" field in the Entry table:

let cat := text(Category);
(select Subcategory where Category = cat) order by Name

I'm selecting the Name of the subcategory as the dynamic value. Instead of the expected values, I get an empty combobox. I had this working a while ago, so I went back to that test database and now it is broken there as well.

Any suggestions on how to get around this? Thanks!

29 replies

null
    • Alain_Fontaine
    • 2 yrs ago
    • Reported - view

    If you really want to use a "select" statement to fill the dynamic choice, you could write:

    let cat := text(Category);
    (select Subcategory where Category.Name = cat) order by Name

    The problem with the original formula is that the identifier "Category" designates the table "Category", the dynamic choice in the table "Entry" and the N:1 relation in the table "Subcategory". In the context of the "select", it designates the relation, so you need to indicate which field of the related table must be compared.

    Since you do have a relation between "Category" and "Cubcategory", it would perhaps be better to use it directly and write:

    record(Category,number(Category)).Subcategory order by Name

    • Kevin
    • 2 yrs ago
    • Reported - view

    Ah, yes. That makes sense regarding the need for the field name. I'm not sure how this used to work, but that's neither here nor there.

     

    I hadn't used "record" before, but that is much cleaner and, indeed, it is working!

     

    Thanks so much!

    • Rvl
    • 1 yr ago
    • Reported - view

    Hey Gals und Guys,

    I have a very similar problem as above but in spite of trying for days on end now, I cannot make my setup work.  My main problem is certainly twofold:  a) I am not experienced enough with Ninox to do what I want to do and b) a big problem surely lies within my data relations.

    I am a Conference Interpreter and as such I manage a large amount of vocab in glossaries related to a particular conference.  The glossary section of my Database consists of Main Conference Topics and their related Sub Topics.  A Subtopic will have its own set of Terms with its own set of collocations.  I have set my table structure up as follows:

    - "Main Topics" with one field "Category";
    - "Sub Topics" with one field "SubCategory";
    - "Terms" with various fields for each language variant, pictures, general info, etc.; and
    - "Collocations" to show different forms of a term (hyponym, antonym, synonym, etc)

    A "Main Topic" can have numerous "Sub Topics" and vise versa (n:m relation).  Each "Sub Topic" will have its own set of "Terms" which have their own set of "Collocations" (1:n relation with composition).  My structure looks like the image below. 

    1. I've been able to use the info in the above post to select a single Main Conference Topic in a drop down list with:  (select 'Main Topics') order by Category.  So far so good.
    2. I've managed to create a switch that shows each Sub Topic related to Main Conference topic with:  
      record('Main Topics',number(Speeches.Conference.Topic)).SubList order by 'Sub Topics'   
      see image below titled "Concepte" that correspond to Main Topic : Music
    3. The difficulty I am facing is how to populate the "Terms" table that correspond to the concepts.  My table will  not populate with any of the variants on the above mentioned methods (i.e. select "tbl" or record(tbl, number... etc).  To make things perhaps more difficult the "concepte" buttons are multiple not single choice.  So my "Terms" table should theoretically show me a list of all Terms with any and/or all of the three Sub Categories.

    I know I have given a lot of info and am asking a great deal of someone.  I feel like my problem is very simple and that the experienced eye would see my mistake immediately.    I would be most grateful in any case for any and every bit of assistance someone might be able to offer (in English, German, French or even Italian if need be).

    Many thanks,
    Richard lost in Translation

    • Fred
    • 1 yr ago
    • Reported - view

    Some questions about the the field Concepte:

    record('Main Topics',number(Speeches.Conference.Topic)).SubList order by 'Sub Topics'
    

    1) what table is this field in?

    2) is SubList a reference field, if so then what table does it point to.

    For the view element, Trems, you can try something like:

    let dmcselection := for loop1 in numbers(Concepte) do
            record('Sub Topics',loop1)
        end;
    dmcselection.Terms
    

    Lines 1-3 uses a for loop command to step through the dynamic multi choice field Concepte and then finds the appropriate records. This is why I was asking the questions above about which table Concepte pulls from. If it is NOT 'Sub Topics', then you have to change line 2 to match, but then that will change what happens in line 4.

    On a side note, since you have such a personal question it is best to open a new post and not add to a really old one.

    • Rvl
    • 1 yr ago
    • Reported - view

    Cheers Fred,

    I get practically the same results with your solution that I was getting with mine, but in both cases they are random results.  I get the feeling my data might be corrupt (if that is possible) or with all the different relations between the tables perhaps the index numbers in "Concepte" (I need to change the typo) are not the same indexes in "Sub Categories" ? 

    I added a "fx" field to my "Slides" table.  (To answer your question, "Concepte" and "Terms" are parts of the Table called Slides.)  If I click on a SubCat e.g. "Orchestra Terms" it returns the number "2".  If I look at the ID for "Orchestra Terms" in the Sub Topic Table it is "37".  I also have one "Slide" where the fx field (ConceptNr) is populated although no concepts are clicked.  Is that where my problem lies?  

    I did a "data reorganise" but that did not seem to change anything. I include below a complete screenshot of my relations as well as various screenshots of the fields and tables in question in case that helps you.  

    I am most obliged for your expertise and assistance.  

    Rich

    • Fred
    • 1 yr ago
    • Reported - view
    Richard v. L. said:
    with all the different relations between the tables perhaps the index numbers in "Concepte" (I need to change the typo) are not the same indexes in "Sub Categories" ? 

     That is why I asked those questions. Since you are using a M:M table you need to very sure that your are matching tables properly.

    It all starts in Concepte. What is the root table that the data comes from? Then you have to then trace back to Terms so you can get the right records.

    Can you post a sample of your DB?

      • Rvl
      • 1 yr ago
      • Reported - view

      Fred yeah, i can post a sample. with the paper clip?  i'll need a second to take the data related to people out.  that said, please don't cringe when you see the mess.  i am really just shooting from the hip until I get something I can more or less work with then i will clean it up.

      • Fred
      • 1 yr ago
      • Reported - view

      Richard v. L. That is correct. You save an archive then click on the paperclip to upload the file.

      Don't worry about the mess. You should see mine. It has grown organically as I've learned so it is a mess.

      • Rvl
      • 1 yr ago
      • Reported - view

       it says "ooops, can't upload"  should i take out more data?  is it perhaps to heavy?

      • Fred
      • 1 yr ago
      • Reported - view

      Richard v. L. don't know. never seen that before. yeah, try to keep it simple and small.

      • Rvl
      • 1 yr ago
      • Reported - view

      Fred let's try this... i have taken all speeches out except 4. those are ones where you will see the most mayhem.  

      • Rvl
      • 1 yr ago
      • Reported - view

      Richard v. L. the fourth speech on 9th Jan is showing some really strange stuff.  only numbers by concepts instead of the terms as in the others.

      • Rvl
      • 1 yr ago
      • Reported - view

      Fred at least i saw why the a.m. speech was showing such strange results.  there were no more sub topics connected to the main topic.

    • Fred
    • 1 yr ago
    • Reported - view

    Since Concepte is based on 'topics n:m' table (but called 'Related SubTopics' in the Main Topics table), the formula for the view 'Trems' is:

    let dmcselection := for loop1 in numbers(oldConcepte) do
            record('topics n:m',loop1)
        end;
    dmcselection.'Sub Topics'.Terms
    
      • Rvl
      • 1 yr ago
      • Reported - view

      Fred i don't know how it works but it works !!!!!!! yeah !!! youppiii !!!! thank you Fred. you are a star, santa and a goldmine all rapped up into one.  i knew my problem was small, and with the right set of glasses, someone would see it. thank you for finding "the missing comma".  I'll dissect it now and try and see how i can replicated it elsewhere if need be. 

    • Fred
    • 1 yr ago
    • Reported - view
    Richard v. L. said:
    I also have one "Slide" where the fx field (ConceptNr) is populated although no concepts are clicked.

     This is a bug in Ninox. Check out this post for more info about this.

    If you see this then I would recommend that you create a button that zeros out Concepte:

    Concepte := 0

    Then you can be sure to get the correct records when you do select the choices.

      • Rvl
      • 1 yr ago
      • Reported - view

      Fred Txs for the heads-up.  I had indeed deleted (or changed) records in my base-table.  As to the above, when you say "create a button" to zero out the field, why not just start the initial script with "Concepte := 0"?  So before the "Let dmsc... etc" in line 1 above?

      Further, now that we have managed to get the "terms" table to properly populate, could you help me to show only the relevant fields for each speech?  

      Each speech is based on different language pairs and directions depending on the speaker.  You see on "slides" I have a Language VO (source language) and a Language VI (target language).  Can I get my "Terms" table to show for example the German column first when German is the "VO" and say English second when that is the "VI"?  Is that possible to do?

    • Fred
    • 1 yr ago
    • Reported - view
    Richard v. L. said:
    Each speech is based on different language pairs and directions depending on the speaker.  You see on "slides" I have a Language VO (source language) and a Language VI (target language).  Can I get my "Terms" table to show for example the German column first when German is the "VO" and say English second when that is the "VI"?  Is that possible to do?

    The only way I can think of, which doesn't mean it is the only way, is to create a view for each VO language first. But the problem with that is you have to then create a view for each possible combo of language (German to English, English to German, German to French, French to German, etc.).

    I remember someone had a way making something like this more flexible, but I can't remember how to search for it.

    Another way, off the top of head, is to create another table that would pull the appropriate data from the Terms table depending on language selected.

    I'll try to make test db in the next few days.

      • Rvl
      • 1 yr ago
      • Reported - view

      Fred 👍

    • Fred
    • 1 yr ago
    • Reported - view

    Ok take a look at this attempt. I've used a "dashboard" to interact with your data.

    It will open to a new table "Dash1". From there you select the Conference, then the Speech and it will show you the languages of the speech and then bring up the Slides. Then Concepte field will get populated. Once you select an choice in Conepte then the Terms view will populate. You will see that it will show the terms that match the languages of the speech.

    In the Terms table you will see a new tab called "Work Area" and that is where the new fields are. You can see that I created fields that that look to the Dash1 table to figure out what data to pull.

    It is very crude. You can show any data you want on this dashboard. I've only done just a couple. You can edit or update data through the dashboard. I've found them to be very helpful as I don't have to go jumping around between tables to add new or update records.

      • Rvl
      • 1 yr ago
      • Reported - view

      Fred Brillant!  It works.  It appears at first glance that your "dashboard" is simply another table connected to the original structure that auto-populates on opening?  I will need a bit to digest it and possibly try to make some adjustments to fit the on the spot setup in the booth, but I am most grateful that you found me. 🙂  

      Thanks, for what looks like a Master Piece in comparison to my blotched canvas.  

      • Rvl
      • 1 yr ago
      • Reported - view

      Fred Hey Fred,

      I've been able to replicate all but one thing -- the most important thing obviously.  

      1. You've created a tblDashbord and related it to tblConfs and tblSpeeches but not to tblTopics.  (same structures I have)
      2. On tblDashboard you've created new fxFields called "Language VO/VI" and basically said they are equal to the Languages indicated in tblSpeeches (I've done the same.)
      3. In tblTerms you've added new fields notably to "first(select.Dash1)" then choose the language indicated in tblSpeeches (either VO or VI).  

      I've done the same as well but not used a Dashboard (I want to keep the original dB-structure) but I can't connect the dots. In tblTerms I've created a "first" ref to tblSpeeches (like you).

      Then I've created new fields (tblTerms) to designate which language is which but the command (like yours w/o dash1Selections) i.e. "Speeches.LangVO" gets rejected. The editor is not making the connection to tblSpeeches.

      Any ideas where the missing comma is? It's not a syntax error.  Both the tblName and fieldName are correct.

    • Fred
    • 1 yr ago
    • Reported - view
    Richard v. L. said:
     It appears at first glance that your "dashboard" is simply another table connected to the original structure that auto-populates on opening?

     It doesn't auto populate. It is showing my recent selection. You can always tell Ninox to clear out any selection if you want it to be fresh everytime you open it.

    • Fred
    • 1 yr ago
    • Reported - view
    Richard v. L. said:
    I've done the same as well but not used a Dashboard (I want to keep the original dB-structure) but I can't connect the dots. In tblTerms I've created a "first" ref to tblSpeeches (like you).

     the issue here is that you will only get the first record from the Speeches table.

    The Terms table does not have a reference field to Speeches so you can't just use Speeches. I can in the dashboard because I have a reference field to Speeches that you choose a record from. Which then allows me to use the link to get data from that record.

    Creating the Dashboard does not really affect your structure. You can always delete it later if you feel like it doesn't fit your needs.

      • Rvl
      • 1 yr ago
      • Reported - view

      Fred   sorrry, what i mean by "keep my structure" was, I did not want another button to push.  meaning once i'm in the booth, i've selected the speech, i just go down the list of slides as the speaker speaks and i have the terms right there if need be.  the dashboard was another something to adjust but that said, I am going to try something and see if it's viable.  

Content aside

  • 1 Likes
  • 1 yr agoLast active
  • 29Replies
  • 647Views
  • 2 Following