0

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!

29replies Oldest first
  • Oldest first
  • Newest first
  • Active threads
  • Popular
    • Fred
    • Fred
    • 2 wk 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.

    Like
    • Fred So, Maestro, I've now added a small simple "dashboard" to my live dB so that I can continue to work with it.  It is not as elaborate as yours but it gets the job done, namely connect the "Speech" to the 2 "languages" in order to filter the "Terms".  It works fine but I feel there must be a more elegante way to get around this extra step. 

      Like
    • Fred
    • Fred
    • 2 wk ago
    • Reported - view
    Richard v. L. said:
    It works fine but I feel there must be a more elegante way to get around this extra step.

    Personally, I think the Dashboard is a more elegant way of accessing your data. It seems like now there is a lot of clicking and jumping around tables to find the data you want. The Dashboard is one table and can pull all relevant data that you need.

    It does take a bit of time to figure it out what data you need, then how to get it.

    Like
  • Fred said:
    It seems like now there is a lot of clicking and jumping around tables to find the data you want.

    Hey Fred, it may seem superfluous or like a lot of jumping around from the outside but that is exactly what one needs in-process in the Booth. A conference is divided up into different speakers/speeches and each speech is divided up into subsections that represent that part of the speech that we are hearing at that moment live.  With the structure set up like it is, we can add other visual input "slide by slide" and have corresponding vocab on-screen if need be.  

    I can't understand why I can't reference my "Source" and "Target" languages directly from "Speeches" (and do away with "Dashb" altogether).  I can then carry the language pair over to "Slides" and call up the result up from there to build my columns. 

    You can see from the above, I have the cue I need but the "Terms" populate (meaning all the other columns are present with info) with the exception of the language columns that remain empty.  

    Like
    • Fred
    • Fred
    • 9 days ago
    • Reported - view
    Richard v. L. said:
    I can't understand why I can't reference my "Source" and "Target" languages directly from "Speeches"

    It is because Terms and Speeches are too distantly related and with too many 1:N and N:1 relationships in the way.

    If we follow Terms. It is in a N:1 to Sub Topics which is in a N:1 to Main Topics. And that is soft linked to Conferences through a dynamic choice field.

    Then if we follow Slides, that is in a N:1 with Speeches which is in a N:1 with Conferences.

    I've updated your sample to show one way of doing it. You will see I've created a new table called global and created two fields. I modified the oldconcepte field Trigger on update to copy the data from VO Language and VI Language. Then if you scroll down to the view element View you will see the proper languages.

    Then in the Terms table I've updated the fields in the work area tab to now point to the global table to get the proper information.

Like Follow
  • 9 days agoLast active
  • 29Replies
  • 412Views
  • 2 Following