Relationship Problem

[newtothegame]

I have a text field, called "Indx", in a main file which is auto calculated as follows: Client State&GetAsText(GetAsNumber(Date), with the field, "Client State" being a two or three letter abbreviation for the State where a client lives and "Date" being the date applicable to that record within the file. Note also, for later reference, that "Client State" has been looked up from another file, as opposed to data entered in the same layout, (I'm not sure whether that's significant or not but I thought I should mention it just in case).

So far, so good - all working.

My problem arises when I try to use "Indx" to create a relationship with another table either within the same file OR another separate file, the other table being a list of public holidays for the nation, (hence the concatenation of State and Date, because different states have different dates).

I've established the relationship between the two tables, and I’ve setup the text in a matching, "Indx" field in the second table to be exactly the same as the “Indx” field in the first table, i.e., State&GetAsText(GetAsNumber(Date), and I've established a value list items field in the first table to check this, and which verifies it to be ok. However, when I try to return a lookup value back from a "Return" field in the second table to a "Return" field in the main table, (just a single digit number), nothing happens. When the lookup didn’t work, I inserted a portal into the layout on the main file to test whether the relationship match is happening and it comes up empty as well.

While most of the lookups in the main table should come up with a fail because most of the dates are not public holidays, a record containing a public holiday date should come up with a positive result. However, no matter what I try, I get nothing - either in the lookup or the portal.

I think I've checked everything - carefully matching field formats to all relevant fields, etc. And, understanding that the match field in the second table is calculated, I've even tried exporting the calculated results in both the main and related tables to separate files so that the related fields contain bare, uncalculated data and then tried to establish a relationship through those files with the equivalent fields. Still nothing.

Can anyone tell me what I'm doing wrong this time? (You all did good last time! : )

Thanks

[newtothegame]

OK- In addition to the above I just exported the bare data only to two separate, independent files, one as a main file and the other as the related file and established the relationship between the "Indx" fields in both files and the data in the "Return" field STILL won't come up when a match is established.

The only two things I can think of is that there is a necessarily a mix of alpha and numeric characters in the match field, (a typical example of the "Indx" field contains data, NSW743131) or that the lookups won't work after a certain number of failures in previous records. Both scenarios make no sense and I hope would not be restrictions created by FMP, but I can't think of anything else.

I'm stumped as lookups are working fine elsewhere in the original and other dbases. Note again that the data didn't show up in the portal I created in the original dbase, so I'm thinking it must be something to do with the data format.

Anyone with any clues?

[comment]

hence the concatenation of State and Date, because different states have different dates

This is not necessary: you can extablish a relationship matching on BOTH state and date. This will eliminate the conversions and ease troubleshooting.

I'm thinking it must be something to do with the data format.

More likely it has something to do with the data itself. Also check that the matchfields are indexed in tha Holidays table.

[newtothegame]

Thanks Comment, as always for your help. I had made sure the match fields were indexed, so we're ok on that.

When you say I can match on the two fields, State and Date, are you suggesting that if I link the two fields to the corresponding State and Date fields simultaneously in the relationships graph, that FMP will treat the two relationships as a single, unique match for lookup or other relational purposes? Presuming that's what you meant and it did seem like a good idea, I tried doing it but unfortunately I still didn't get a return in the lookup. Please let me know if I misunderstood you.

I guess up until now, I've been trying an approach similar to that suggested in:

http://help.filemaker.com/app/answers/detail/a_id/5244/~/relationships-and-lookups-based-on-two-fields

(unless I'm misunderstanding the suggestions in that page) whereby I've combined the data in the two fields into one calced field in each of the two files to make a match. I've done this on numerous occasions while setting up lookups in other spreadsheet or database applications and never had too many probs, as long as I've been careful to get field formats (and sometimes in the old days, field lengths) to match. That's why I'm so surprised I can't get it to work here.

Another clue: If I set the "If no exact match" option in the lookup to "copy a higher value", I get the returned data in the field that I want - but in all records, rather than just the ones whose dates are public holidays, which is what I want. If I set the option to "copy a lower value", I get nothing in any record. If I set to "do not copy", which should be the correct option, I also get nothing in any record. So although the basic relationship seems to have been established, there appears to be just something awry between the data it's looking to or from. Does that suggest anything?

[comment]

unless I'm misunderstanding the suggestions in that page

No, but those suggestions apply to version 6 and earlier. Starting with version 7, a (single) relationship can have multiple predicates:

http://www.filemaker.com/11help/html/relational.11.7.html#1027907

Does that suggest anything?

It suggests an exact match was not found - which strengthens the suspicion that the data is not what you think it is. Perhaps there are some hidden characters (e.g. spaces, carriage returns, non-printing characters) that are not obvious to visual inspection. Try re-entering the state and the date in the two records that are supposed to match and see what you get.

[newtothegame]

"Perhaps there are some hidden characters (e.g. spaces,..."

Funny you should say that, comment, you little genius!!! :

I happened to do a bare export of just the Indx field to a .tab file which I was going to throw up to the board here for us to look at (I didn't have too many records, just a few to 'play' with) and I noticed there was a leading space character in the State field, which was generated for reasons too complicated to describe in detail here - briefly - varying lengths of state abbreviations here, combined with pulling the data up from another dbase created by another person who hadn't separated address, state and post/zip code in their address field.

I changed the downput of the state field and its concatenation to the date to get rid of the leading space and Eureka! end of the problem! I came back here to tell you I'd fixed it and you'd picked the problem in one - without having the data there to look at - giving further weight to my earlier claim of you being a genius.

Thanks mate. Once again, you've done good! :)

[newtothegame]

By the way, I did use the double relationship correctly after you suggested it and according to the link you passed on http://www.filemaker....7.html#1027907

While it didn't solve the initial problem, I'm pretty sure it's given me the way to do the job without the need for the concatenated "Indx" field in the main and related tables, just as you said. I'd actually inadvertently done that in past "playing" and didn't fully appreciate the significance and usefulness. So, thanks also for the tip about that and I'll give it a try today, now that I can get the thing working at last!

[newtothegame]

One last addendum to this thread: I did try the double relationship that comment suggested and it works perfectly, eliminating the need for the concatenated index field, so I've solved one problem and learnt something new. Thanks again and good job!