New user- using filter on relationship field

Discussions for the HanDBase for iPad and it's conduits and desktop add-ons

New user- using filter on relationship field

Postby Beejay » Sat Nov 15, 2014 3:12 pm

Hi I am new to this db but have experience in other databases.
I have set up a relationship field which contains names from another database but when I try to use the filter on this field nothing is selected.
Is it possible to do this or is it not possible with a relationship?
Beejay
 
Posts: 4
Joined: Sat Nov 15, 2014 2:58 pm

Re: New user- using filter on relationship field

Postby Brian_Houghton » Sun Nov 23, 2014 10:33 am

Hi,

It is possible as I use the feature frequently. Very sorry that it is not working for you.

If possible, could you send me a copy of your databases so I can double check them here? If so, please send both databases to me at support @ ddhsoftware . com (minus the spaces) with a brief reminder of the issue.
Kind Regards,
Brian Houghton, DDH Software
Brian_Houghton
 
Posts: 2246
Joined: Wed May 20, 2009 8:30 am

Re: New user- using filter on relationship field

Postby Beejay » Sun Nov 23, 2014 1:18 pm

Thanks Brian for your response but I don't think I explained the problem too well and I think I have found a solution within the existing posts.
The databases I have imported (they were previously tables in an access db) consist of data I have transcribed from air ministry operations records from the Second World War for a specific squadron.
In one database I have a list of all the airmen and in another I have a list of all the crews flying each aircraft. Each record in the crews database contains 11 links to the airmen database (the number of airmen in a crew of one aircraft).

A simple example is as follows:
Airmen database
Id name
1 Smith
2 Jones
3 Johnson
4 Evans

A single record in the crews database could be
Ref pilot navigator gunner bomb-aimer
23 2 3 1 4

I then added relationship fields for each crew member so the same record then looks like
Ref pilot navigator gunner bomb-aimer p2 n2 g2 b2
23 2 3 1 4 Jones Johnson Smith Evans
But when I tried to sort or filter on p2 or n2 or g2 or b2 nothing happened.

I then added a new field for each crew member which were Conditional fields that did the following:
IF pilot is equal to pilot then
Result is
p2
Else result is
p2

This works in sorting and filters but is a bit long winded as there are 11 airmen in a crew and means setting up an extra 22 fields. I was wondering if there was a quicker way of achieving this.

I have not fully set up the database yet as I have been playing and experimenting to see how it works.
Sorry to be so long winded I hope I have made it clear what I am trying to achieve.
The actual access database had several more tables all linked like the example above.

I use the database for supplying info to people looking for information concerning relatives who served in the RAF during the war
Brian
Beejay
 
Posts: 4
Joined: Sat Nov 15, 2014 2:58 pm

Re: New user- using filter on relationship field

Postby mjhanna » Mon Nov 24, 2014 12:30 pm

Hi Brian,

You are correct that there is an issue in sorting or filtering on Relationship fields. While a Relationship field can "Show" some field from the "other" database, the Relationship field itself does not actually contain that value. As I understand it the field actually contains some internal pointer to the other record and its value. That is not a problem if all you want to do is "Show" that retrieved value, which is the most common, but is an issue for sorting or filtering on that value within the retrieving database.

However there is a simple "workaround" when using Relationship fields, but what specific actions are needed depend upon the type of data in the field which is being retrieved. At minimum you will need to create a second companion field in the retrieving database for each field retrieved which is also intended to be used for filtering or sorting.

If you are retrieving text (which your examples appear to indicate) then you need to define a companion Conditional field which performs a "do nothing" condition on the text retrieved by the Relationship field. An example condition is:
IF FIELD (Relationship field) IS EQUAL TO
FIELD (Relationship field)
OUTPUT IS (Relationship field)
ELSE OUTPUT IS (Relationship field)

The "Relationship field" is the same field in all four lines above. 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 this Conditional companion field's output.

If you are retrieving a number then you need to define a Calculated companion field which performs a "do nothing" mathematical calculation on the number retrieved by the Relationship field, with the Calculated field having the appropriate numeric Output. Two obvious example calculations are simply multiply by one, or simply add a zero. As above define your sort or filter on this Calculated field instead of the Relationship field. Again you would display the retrieved numeric Relationship field value but keep hidden the Calculated companion field's output.

If you are retrieving either a date or time value then you require a companion field in both the source database and the retrieving database, and you retrieve the source companion field. First, in the source database define a Calculated companion 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 Calculated field hidden in the source database. In the retrieving database define a companion Relationship field which retrieves this Calculated Integer field, not the actual date/time field. Finally in the retrieving database define a Calculated companion 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 format. Now you can define your sort or filter on this Calculated field. In contrast to text or numbers, in the retrieving database you would keep hidden the output of the integer Relationship field but instead display the calculated date or time field.

Hope this all makes sense and gives you ideas,
Michael
(Only a user and not affiliated with DDH Software, but working with computer databases for over fifty years.)
mjhanna
 
Posts: 156
Joined: Tue Oct 15, 2013 10:56 am
Location: New Mexico

Re: New user- using filter on relationship field

Postby Beejay » Wed Nov 26, 2014 4:30 pm

Thanks a lot Michael ,
I had just about worked this out for my database but was not sure if this was the correct way to do it.
Thanks for the details I think I now know what to do.
In my case each record in the crews database contains 11 names so I must create a conditional field for each crew member which I will do, it just seemed to be a bit long winded. Maybe I should consider redesigning the database to work in a different way. My initial reason for porting this database was to just import the data from my Access database.

I have now purchased the forms add on and will be trying them out so no doubt I will be asking more questions

Regards
Brian
Beejay
 
Posts: 4
Joined: Sat Nov 15, 2014 2:58 pm

Re: New user- using filter on relationship field

Postby mjhanna » Wed Nov 26, 2014 6:52 pm

Yes, there may be value in thinking about redesigning the database. If you need to retrieve multiple field values from the same record in another related database, you should take a look at the DB Popup field. Check out the "Group" attribute of a DB Popup field. From the User's Guide:

"By setting this value to something other than 0 (0 disables this feature), you can assign multiple DB Popups, up to 10, to the same group. Then, whenever any of the DB Popups in a group are selected and a record in the other database is selected, all items in the same group will be filled out automatically with their appropriate field values."

This is a handy way to propagate a number of fields associated with the same record in a related database simply by clicking on any one of the fields and finding the desired record.

Hope this gives you ideas,
Michael
(Only a user and not affiliated with DDH Software, but working with computer databases for over fifty years.)
mjhanna
 
Posts: 156
Joined: Tue Oct 15, 2013 10:56 am
Location: New Mexico


Return to HanDBase for iPad

Who is online

Users browsing this forum: Bing [Bot] and 1 guest