About text fields (sorting, searching, pattern, and value from another entity)

[Didou12]

If you need to get information from parents (for example, or another entity) or want to create a "pattern" of text that you would like to sort and search through the listing (maybe in a report), here are some tips. I also described each text fields.

By the way, I just tested with text like "hello1" and "hello2" and verify if it's okay. I think it's okay too if it's just numbers (or maybe not if the order is not : 1, 2, 3, 10, 11, 23, but : 1, 10, 11, 2, 23, 3). Let me know if you try it with numbers.

If you have this question, see at the end of this post : How I can have a field with a value from another (or parent) entity, which is dynamically up to date, sortable and searchable in the listing ?

• Input field:

Stored in the database.
Sortable.
Searchable (if you enable the option).

• MySQL formula:

Code: Select all

parent_entity_item_value(12,345)

To get information from parent (12 is parent entity, 345 is the field). Just use one time/one field because is too greedy.
Dynamic (so the value is always up to date and not stored in the database).
Can use field from parent entity.
Sortable.
Not searchable.

• MySQL query:

Code: Select all

Entity : the parent entity
Field : [123]
Condition : msq.id = e.parent_item_id

To get information from parent (123 is the field in parent entity, msq. is for selected entity in the list and e. is for current form entity).
Dynamic (so the value is always up to date and not stored in the database) if you enable the option OR stored in the database.
Can use field from parent entity.
Sortable.
Not searchable.

• Ajax query :

Code: Select all

$data_query = db_query("select field_123 from app_entity_45 where id = {$_POST['parent_item_id']}",false);
if($data = db_fetch_array($data_query))
{
  $value = $data['field_123'];
  echo $value;
  $form_field_value = $value;
}

Field 123 is in the parent entity 45.
or use

Code: Select all

$value = [123];
echo $value;
$form_field_value = $value;

But at this time, with this second method, there is an error for the fields which contains a string (I posted on the bug report forum).
Field 123 is in the entity parent.
Stored in the database if you attribute the value at "$form_field_value", if not, this field will be just used during the form with "echo".
Can use field from parent entity.
Sortable.
Not searchable.

• PHP code :

Code: Select all

if(!strlen($current_field_value))
{      
    $output_value = [123];
}
else
{
    $output_value = $current_field_value;  
}

123 is a field in the parent entity. Use this code if you want to store a value in the database (and not to overwrite when editing)

Code: Select all

$output_value = [123];

Use this code if you want to overwrite when editing, or if you check the "dynamic execution" (so not store in the database, if you want to save space in the database or want to have the value up to date).
Dynamic (so the value is always up to date and not stored in the database) if you enable the option OR stored in the database. Buy you always need to attribute a value at "$output_value" because it's the main purpose of this field (or not, if maybe you need to execute php code after adding or editing a form).
Can use field from parent entity.
Sortable.
Not searchable.

• Text pattern :

Can't use [123] for field from parent entity. Just fields from the current form/item.
Stored in the database.
Not sortable.
Not searchable.

• Static text by pattern :

Can't use [123] for field from parent entity. Just fields from the current form/item.
Stored in the database.
Sortable.
Searchable (if you enable the option).

• Value from parent entity :

Can use field from parent entity (it's the main purpose).
Dynamic (so the value is always up to date and not stored in the database).
Not sortable.
Not searchable.

• List of records by MySql query :

Code: Select all

Entity : the parent entity
Condition : e.id=[parent_item_id]
Heading template : [123] or empty for the heading field

Can use field from parent entity.
Dynamic (so the value is always up to date and not stored in the database).
Not sortable.
Not searchable.


How I can have a field with a value from another (or parent) entity, which is dynamically up to date, sortable and searchable in the listing ?

At this time, I don't know how to have a dynamic field up to date.
But for the sortable and searchable in the listing, from parent entity, you can use this workaround :
Value from parent entity field (and hide it in the form and on the item page) to get data
Static text by pattern field with the field ID of the previous field,
so after saving, you have the text stored in the database and you can use it with sort and search.

For another entity, you can use MySQL query field to get data (with disabled option of dynamic execution).