MySQL Query returns FK to Global List

[weitau]

The MySQL query works great to reference fields in a related table. However if that field is a List type, what is returned is the foreign key, not what the FK represents.

For example, I have two tables,
People
id = 3
[100] Name (heading) = Bob
[105] Country = 14 --> but displays France because its a List field type, using Global list

Person
[200] Name = 3 --> displays the Heading of Bob because field type is Entity dropdown
name is the foreign key to table People
[205] Country = MySQL query --> displays the value of 14

Global list
id Name
10 Poland
14 France

How do I get Person.Country to show the value of "France" instead of 14?
Many thanks!

[support]

I think you have to use field type Formula where you create supquery to app_global_lists_choices table to get value that you need. Open phpmyadmin to check app_global_lists_choices table

[weitau]

Yes, I was considering this and some form of GetValue(). I can do SQL, but I'm unfamiliar with the way Rukovoditel references fields in the current table vs joined tables. For example when I use [243], is that the field in the current table or the table that has field_243?
What is the syntax and usage for e.field_xxx?

My other concern is that for hundreds of rows, this nested select would consume too many resources. Perhaps it's only run once on create or update?

[weitau]

There should be a field type that will store the actual value from a list, not just the index value

Excel lists work this way. Once the user selects the choice, then the choice then fills the cell.
This works as a data entry validator but it uses more storage space but in today's age, storage is cheap.

The choices will still come from the app_global_lists_choices table.

[weitau]

SOLVED

I think you have to use field type Formula where you create supquery to app_global_lists_choices table to get value that you need. Open phpmyadmin to check app_global_lists_choices table

Thanks, I created the subquery as you suggested. I figured out that the MySQL Formula runs only on update and insert because I manually replaced index value to the actual Country name I wanted, and it displayed on the record. At least until the next update.

SELECT name FROM app_global_lists_choices WHERE id IN
((select app_entity_28.field_259 FROM app_entity_28 WHERE app_entity_28.id = [354]))

I still think that in the spirit of Low or No code app development, you would want to add a field that stores the Country name and not the index value to the Country name.

[support]

I still think that in the spirit of Low or No code app development, you would want to add a field that stores the Country name and not the index value to the Country name.

This is not right way. If you did mistake in Country name and changed the name it should be changed in all records. So that is why relation is by ID.
If you need to store Name in item table then you can use "Static text pattern" field type that will save just text in record.

[weitau]

Yes, I understand your reasoning. Someone could mistype Country and you have data integrity issues.

My suggestion was to have a new List type field store not the index value, but the actual lookup value itself. So the field dropdown allows the user pick France, and instead of storing an Integer value for France, store the text string for France. The user will not be able to type into this field, only make a selection.

Still the "Static Text Pattern" is a another great feature which is currently not documented. May I offer this text to be added to your documentation?

Static Text by Pattern is a field type that stores the text lookup value by forcing text input into a text pattern. The pattern is defined by a List type field in the same table. Another use for Static Text is to concatenate strings.

For example, you have two fields in your table, [color] and [toy].
You can create a new field called [Sentence] which is a "Static Text by Pattern" with the pattern defined as
{I painted my [toy] with [color] paint}

So if the record has values [toy]=BIKE and [color]=BLUE, the text string "I painted my BIKE with BLUE paint" will be stored in the database in the [Sentence] field.

With this method, you can have Postal Code and City as separate fields, but have another field concatenate these fields in an address line as "Los Angeles 94030" or "75006 Paris"

[marajah]

Hello everybody
I need to verify that one of the fields of the form, chosen from a global list to make it easy and simple, has a correct value compared to two other fields of the form (these two fields are calculated with a sql query). I have to do this check before submitting the record to the DB.
I hate to do more work than what is needed... So I did everything in the Javascript of the form, calling the text value of the option and converting in number.

I have a dropdown global list (i cut off some values, the real list is a bit longer). This is its html code in the editing form, collected with an inspect:

Code: Select all

<select name="fields[1094]" id="fields_1094" class="form-control input-small field_1094"
    data-placeholder="Select some options">
    <option value=""></option>
    <option value="955">0.6</option>
    <option selected="" value="956">0.7</option>
    <option value="957">0.8</option>
</select>

There are two fields, input numeric, that are the range. The value chosen from the previous list must be included in the range. These fields are 1095 and 1096.
I opened the Entity, View configuration, Form configuration and in the onSubmit tab I wrote this javascript:

Code: Select all

// controllo grado complessità
if ( (Number($("#fields_1094 :selected").text()) >= Number($('#fields_1095').val()) && Number($("#fields_1094 :selected").text()) <= Number($('#fields_1096').val())) || $('#fields_1094').val() =="" )
{
  return true
} else {
  alert('Selezionare un grado di complessità compreso nell\'intervallo');
  //to enable Save button
  $('.btn-primary-modal-action').prop('disabled',false);
  //to stop form submit
  return false;  
} 

This way the text value of the item fields_1094 is checked against the range fields 1095 and 1096; when it's included between (it can also be equal to the extremes) the range the record will be saved; if the field_1094 is not chosen, meaning that it's empty, the record will be saved; if the field is not included in the range an alert will be shown and the window will not close.
I hope it could be useful to someone, it's my 2 pennies worth