I have been driving myself crazy trying to get my file to relate correctly. I'm trying to create a self-join with a field I also use to relate to a second file. I am trying to find duplicates. There should not be any duplicates at this time but my duplicates calc field is showing that I have some when I don't really.

My match field is a concatenating calculation of 4 fields. I found recently that there was a size issue, but I thought I fixed it...maybe not.

The 4 fields are: Drain,Fund,Account,Year. (this is a file storing beginning balances) My calc (text) is Drain & " - " & Fund & " - " & Account " - " & Year. An example would look like A0100 - 800 - 100 - 2005. Prior to this I had no spaces between the dashes. Is filemaker still viewing this as one word?

Here is the type of info I'm getting in my portal:

Current record: R0700 - 924 - 660.4 - 2005

Portal:

D1053 - 806 - 660.4 - 2005

L3603 - 807 - 660.4 - 2005

- 826 - 660.4 - 2005

- 843 - 660.4 - 2005 etc

As a side note: My year is not showing up in the field. I can only see it if I click on the field (and the width of the field is adequate to show it.

Any suggestions on how to fix this would be greatly appreciated!

Can you post a sample of your file?

Thanks for responding. I just figured out what the problem was with my relationship.

My scenario is I have three files that are lists. (Drain, Fund, Account) I have a script that loops through each one counting total records and then creates new records in my 4th file (beginning balances). There is one record for every combination needed. The problem is that in several of the fields someone typed in a blank space after the actual data. In one case it was "O11-00 " and should have been "O11-00". Is there a way I can prevent these blanks?

You could do this several ways. The easiest would probably be to validate the match field with

not PatternCount( field, " " ) and not PatternCount( field, "

Thanks Queue!

The match field just concatenates several primary fields to create basically a storage area for a summary of all the records for that combination. We assign numbers for each drainage district based on it's name etc... Widgets drain may be W01-00. I'll have to think through your idea of auto-enter fields though. Anything to get around having to rely on user entry is the best bet. Thanks!

Queue,

The match field is a calculation. Because of the nature of what I'm doing with this match I figured I would actually correct this problem within the match field's definition by creating this calculation:

Substitute(Substitute(Substitute(Drain,"-","")," ",""),"

That will work too. You could make it a little less redundant though.

Substitute( Substitute( Substitute( Substitute( Drain & "|" & Fund & "|" & Account & "|" & Year, "-", "" ), " ", "" ), "

Thanks! I'm always taking the long way.