Relating to an empty field?

[Steve G]

I'm trying to set up a self-defining portal and I'm having some difficulty.

I have a table ("Recipients") which has a text field ("Name") and three date fields ("Nomination Date", "Award Date", and "EMPTY"). The "EMPTY" field is just that: empty, no data in it at all, and it is a global field.

"Recipients" has two relationships defined. The first is with "Recipients 2" which relates "Name=Name". The second is with "Recipients 3" which relates "Name=Name" *and* "EMPTY≠Award Date".

Portal #1 on my "Recipients" table points to my first relationship, thus giving me a list of all awards that a given person has been nominated for and/or received. This works fine.

Portal #2 on my "Recipients" table points to my second relationship, thus giving me a list of ONLY awards that a given person has been nominated for. The condition of "EMPTY≠Award Date" would be the qualifier here; if "Award Date" is empty then the person hasn't received the award, therefore the person was nominated but did not win.

The problem is portal #2 and my relationship with "Recipients 3" is not working properly UNLESS I put some data into the "EMPTY" field.

Is there no way to establish a relationship based on the emptiness of a field?

Edited May 11, 2009 by Guest

[Tpaairman]

NO, you cannot do that. What happens with a relation is Filemaker is linking the two tables based on the the one field in each. Think of a portal as a kind of search function. So let's say you are using the names as the related field in both tables here, your portal is displaying all the records from the related table that have the name that matches the name in the table you are looking at. If you field is empty, there is nothing for FMP to match to.

Here's what you need to do. Change EMPTY to a calculation. When the calculation window comes up, look on the list of functions on the right of the window for CASE (test1; result; test2; result).

Get rid of the stuff in the parenthasis () and make it the following:

Case(Date="";name & "0";name & "1")

Make sure the calculated result at the bottom has text selected. What happens is this field will now look at the date field. If the date field is empty, then this field will have the name and a 0 in it. (jones0) If there is anything in the date field, then this one will show the name and a 1.

You need to have the same type of field in the table your portal is on, but that can be with a script. Regardless, use the join field on both sides for your relation. On the side with the protal, when a name is entered with a 1 right after it, it will show anyone who got the award, and if there's a 0 right after the name, it will show those who did not.

Edited May 12, 2009 by Guest

[Steve G]

Thanks for confirming. Based on your suggestion I found a more simple solution. My "EMPTY" field now has a date in it of January 1, 2000, which is earlier than any conceivable date that would be entered in the "Nomination Date" or "Award Date" fields. I can then compare the values of those fields ("EMPTY < Award Date", for example) and since FM sees an empty field as the lowest possible value, then an empty "Award Date" field would be less than the date in my "EMPTY" field and would give me the result I need.