0

Dynamic Choice Fields reference table

Hi Helpers,
I'm having difficulty solving the following code that is used to enter custom data into a table referenced by dMC fields.

As an example,
The last/highest number(row) in the Number column of the 'Dynamic Field Data' table is 100.
The last number row of the 'Color' Column is 12. When I enter a new color it is placed in row 101.
Any Ideas how the code can be altered so that the new color is placed in row #13?

With this code the table grows quickly and the data is scattered.

thanks for looking!

let xChoice := 'New Data';
let xType := number('Field Name Choice');
let i := (create 'Dynamic Field Data');
switch xType do

case 1:
    i.(Color := xChoice)
case:2
    i.(HEIGHT := xChoice)
case 3:
    i.('Single Letter' := xChoice)
case 4:
    i.(Number := xChoice)
case 5:
    i.('State Name' := xChoice)
case 6:
    i.('State Abbreviation' := xChoice)
end;
'New Data' := null;
 

13 replies

null
    • Fred
    • 1 yr ago
    • Reported - view
    Sam said:
    The last number row of the 'Color' Column is 12. When I enter a new color it is placed in row 101.

    I’m not sure what you are telling us here.

    Is ‘Color’ a field in ‘Dynamic Field Data’?

      • Sam.1
      • 1 yr ago
      • Reported - view

      Fred yes

      • Fred
      • 1 yr ago
      • Reported - view

      I am confused by what you mean by 

      Sam said:
      The last number row of the 'Color' Column is 12.

       If Color and Number are fields in the same table there will always be the same number of records in the table.

      Are you saying that there are only 12 records with data in Color?

      What kind of field is Color?

      • Sam.1
      • 1 yr ago
      • Reported - view

      Fred yes they are fields in the table. There are 100 number records. There are 12 colors. When I add a new color it gets positioned in number 101. Add another record that is color or other and it is positioned in 102.etc

      There are 21 fields. The largest is 100. So, as each field has new data, the table grow quickly.

      When looking at the Color column with the new data—the color records are 1 thru 12 and then 88 blank blocks in the column until the new color data is seen in row 101

      • Sam.1
      • 1 yr ago
      • Reported - view

      Sam that should be —-the new color gets positioned in row number 101of the color column

      • Sam.1
      • 1 yr ago
      • Reported - view

      Fred color is a text field. They are all text fields.

      • Sam.1
      • 1 yr ago
      • Reported - view

      Sam I see that there is one number field. Do you think that matters?

    • Ninox developper
    • Jacques_TUR
    • 1 yr ago
    • Reported - view

    On your case, its easier tu use text value, not number.

    Try this code to inspect the selected option by its name :

    let xChoice := 'New Data';
    let xType := text('Field Name Choice');
    let i := (create 'Dynamic Field Data');
    switch true do
    case xType = "Color" :
        i.(Color := xChoice)
    case xType = "Height" :
        i.(HEIGHT := xChoice)
    case xType = "Single lettre" :
        i.('Single Letter' := xChoice)
    case xType = "Number" :
        i.(Number := xChoice)
    case xType = "State name":
        i.('State Name' := xChoice)
    case xType = "State abbreviation":
        i.('State Abbreviation' := xChoice)
    end;
    'New Data' := null;

    1 - In line 2, replace number('Field Name Choice') with text('Field Name Choice')
    2 - Make sure that the comparison text is exactly the same as the dynamic choice list.

      • Sam.1
      • 1 yr ago
      • Reported - view

      Jacques TUR thanks Jacques! I’ll make the changes and let you know

      • Sam.1
      • 1 yr ago
      • Reported - view

      Jacques TUR The changes were made exactly. The new data was placed starting at the row with the highest number. Same as with the initial code. Is there another change that might work?

    • Fred
    • 1 yr ago
    • Reported - view
    Sam said:
    yes they are fields in the table. There are 100 number records. There are 12 colors. When I add a new color it gets positioned in number 101. Add another record that is color or other and it is positioned in 102.etc
    There are 21 fields. The largest is 100. So, as each field has new data, the table grow quickly.

     Sounds like you may want to consider a sub, or child, table to your Dynamic Field Data table. You will be creating a 1:N (One to many) relationship. Where 1 record in Dynamic Field Data is related to many records in the new child table.

    So the root, or parent, table just has the names of the fields, Color, Number, Height etc. Then you create an entry in the child table that links to the parent record where you keep track of how many of each parent table record you need.

    Take a look at this video for more about relationships.

      • Sam.1
      • 1 yr ago
      • Reported - view

      Fred That’s an awesome idea! I’ll jump on it this evening and let you know.

      sounds like it’s the way to go. 

      • Fred
      • 1 yr ago
      • Reported - view

      Sam Remember in Ninox you create the reference field from the many table back to the 1 table.

Content aside

  • 1 yr agoLast active
  • 13Replies
  • 198Views
  • 3 Following