Using sorting and filtering in Relational feilds

[Gab-Hop]

Hi Dave
On the desktop
I cant seem to make relations that are worked from links, Sort or Filter.
i.e. a relation that uses a linked feild as the other feild name to link it back to the parent DB
Is this a bug or am i doing something wrong

[mjhanna]

I am not Dave, but in the Desktop HanDBase manual under the Sort Database Screen is the warning: "Certain field types are not sortable as they do not contain any sortable data. They are Link Field, Heading Field, Image Field, and the External Field." I have found this also applies to Relationship fields.

For Link and Relationship fields I believe this is because what is actually stored is the pointer to the linked record. For any actions like sorting and filtering the trick is to define an extra field in the retrieving database whose value is based on the related source database value, but which is a type of field that can be used for sort or filter. This requires defining at least one extra field in the retrieving database, and possible an extra field in the source database. What fields need to be defined depend on the nature of the data being retrieved: number, text, or date/time.

Relationship Field retrieves a number
This only requires one new field in the retrieving database. Define a Calculated field which performs a "do nothing" mathematical calculation on the Relationship field value with the appropriate numeric Output. Two obvious example calculations are simply multiply by one, or simply add a zero. Now define your sort or filter on this Calculated field instead of the Relationship field. Usually you would display the retrieved numeric Relationship field value but keep hidden the Calculated field output.

Relationship Field retrieves text
This only requires one new field in the retrieving database. Define a Conditional field which performs a "do nothing" condition on the text from the Relationship field value. An example condition is:
IF FIELD (Relationship field) IS EQUAL TO
FIELD (Relationship field)
OUTPUT IS (Relationship field)
ELSE OUTPUT IS (Relationship field)
Now define your sort or filter on this Conditional field instead of the Relationship field. Usually you would display the text Relationship field value but keep hidden the Conditional field output.

Relationship Field retrieves date or time
These are more complex, and require one extra field in both the source database and the retrieving database. First, in the source database define a Calculated field which performs a "do nothing" mathematical calculation as described above on the date or time field value with the output as Integer. Usually you would keep this field hidden. In the retrieving database define a Relationship field which retrieves this calculated Integer field value, not the actual date/time field. Finally in the retrieving database define a Calculated field which performs a "do nothing" mathematical calculation on the hidden Relationship field Integer value, but specify its Output as the appropriate date or time. Now you can define your sort or filter on this Calculated field. Usually you would keep hidden the output of the integer Relationship field but display the calculated date or time field.

As a further note, since this uses calculations based on Relationship fields, you will want to be sure to have the option "Recalculate on Relationship Lookup" turned on. I "believe" this option currently is only available on the iOS and Android versions, and not the Desktop. You also want to have the hidden field come in field order after the Relationship field since it depends upon that field.

Hope this gives you ideas,

[Gab-Hop]

Thanks for that
It answers my Query's perfectly
You should get on board with Dave and make a great thing Perfect

[Gab-Hop]

Hi mjhanna

Relationship Field retrieves text
This only requires one new field in the retrieving database. Define a Conditional field which performs a "do nothing" condition on the text from the Relationship field value. An example condition is:
IF FIELD (Relationship field) IS EQUAL TO
FIELD (Relationship field)
OUTPUT IS (Relationship field)
ELSE OUTPUT IS (Relationship field)
Now define your sort or filter on this Conditional field instead of the Relationship field. Usually you would display the text Relationship field value but keep hidden the Conditional field output.

I did this and it worked perfictly on the device
But on the desktop I could not get the value to show up when filtering with a conditional field
I need to do this on the desktop in order to print in list view from it
Also after doing this and trying to change it on the desktop it caused sync errors and the DB would then crash on the device and i had to overwrite the desktop with the old Device version of the DB

Have you been able to do this on the desktop ?

[mjhanna]

Yes. Sounds like you made some error when changing it on the desktop? Also, be sure the Conditional field is sequenced after the Relationship field since it depends upon it.

[Gab-Hop]

Hi
I have duplicated this issue using a test DB
and my findings are
1. Conditional fields cannot be used to filter in the desktops current version
2. Conditional fields can be used to filter in the Android current version
3.There is no difference if you use a Text, Relational, Linked, or any other field type as the source for the conditional field the findings are still the same
4.The value typed in the filter on the desktop will not hold in a conditional field
5.The value typed in the filter on the Android will hold in a conditional field and work properly
6.There is no difference with the field order, i.e. the value will still not hold in the filter on the desktop if made from a conditional field

I Believe this is a bug
Is there any other way to filter, from a relational Field on the Desktop