Unable to populate related table in Choice (dynamic) field

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

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

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.

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

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.