1

Reference to items in the same table

I am still new to the field of databases so sorry if this question is badly formulated.

I have a table 'Items' that lists a large number of items that can be associated to one another.
I want to have a field, like a multiple choice, where I can select all the items that are associated with the item in question.

For instance, in my table 'Items':

Item 1
Item 2
Item 3
...
Item 450

When working in the form of item 4, I want to have a drop-down box that shows all the items currently listed in my table 'Items'. From this box I want to select multiple items that are associated with Item 4.

Is there a way to do this?

7 replies

null
    • Paul_Chappell
    • 2 yrs ago
    • Reported - view

    vincent. cattersel It sounds like you need to use a 'Multiple choice (dynamic)' field.  In the 'Dynamic value' property of this field type Select Items, and in 'Dynamic value name' select the field name from the Items table you want to display in the list. 

    • vincent_cattersel
    • 2 yrs ago
    • Reported - view

    Paul thank you for the swift reply.

    So in the dynamic value property I refer to the table Items, whereas in the dynamic value name I refer to a certain field in the table Items, right? I tried to refer to the table under the dynamic values by writing Select Items but somehow I cannot save so I assume this isn't right?

      • Paul_Chappell
      • 2 yrs ago
      • Reported - view

      vincent. cattersel Yes.  If your table is called Items you just need to say select Items in 'Dynamic value'.  Apologies:  it should be select and not Select. Then in 'Dynamic value name' choose which fieldname from the table Items you want to show in the multiple choice list.

    • Fred
    • 2 yrs ago
    • Reported - view

    Just a reminder that dynamic choice fields have a 100 item limit.

      • Paul_Chappell
      • 2 yrs ago
      • Reported - view

      Fred vincent. cattersel Yes.  In any case, no-one wants to scroll through (or choose from) 450 items in a list.  It would probably be better to structure your table to allow the items to be pre-selected by some sort of filtering data.  I'm sure the items can be grouped somehow?  To do this add a new field called, say, "Item Type", and then change the Dynamic value to read:

      let tempType := 'Item Type';
      (select Items where 'Item Type' = tempType) order by 'Item Name'

      As you can see I've added the command to order the items in the list.

    • vincent_cattersel
    • 2 yrs ago
    • Reported - view

    Paul and Fred thank you both for the answers, it worked!

    One more thing, if I make such a Dynamic MC, and I select a x number of items, is there a way to see certain fields of the selected items listed below (for instance the image of each selected item)? Similar like the lists you get when you select have a Table reference?

    • Fred
    • 2 yrs ago
    • Reported - view

    Take a look at this post. It even has a sample DB you can see what is going on.

Login to reply

Content aside

  • 1 Likes
  • 2 yrs agoLast active
  • 7Replies
  • 122Views
  • 3 Following