Please help... This relationship doesn't work!

[machtenx]

Ok, I have two databases, one for Sales Invoices and one for customers. Inside the Customer database, I have a portal that shows the invoices for the particular customer.

It was working fine when I specified the relationship for each record to correspond to Last Name. I realized this was an error on my part when there were duplicate Last Names with different first names.

I tried to fix this by creating a calculation field for FullName using the formula "FirstName & " " & LastName" I created it the same way for both databases but now I get the message "The relationship is not valid and must be corrected before this field can be modified"

for this database I don't really have any other alternatives, this databases function is to display information recorded with a myob program from which we extract data and import it to this one, so otherwise I would create customer numbers, but it's not really an option at this point.

I was told that calculations can be used but are not recommended for relationships. Is there ANYTHING else I can do at this point? I'm pretty lost. Thank you!

 

[kertusa]

Hello machtenx,

Here's what i would try, first thing. Go to define fields and turn on indexing for each of these fields. You should get an error message when you try this that may provide more insight into the issue. Also, make sure both fields are evaluating to the same type of field, Text would seem like the best idea for your purposes.

In general, using calculated, concatenated text fields is not a problem if they are based on stored (non-calculated) fields. So, if FirstName and LastName are regular text fields, I would expect this to work. If either FirstName or LastName are themselves calculations of some sort, then no, this won't work.

Try turning on indexing, double-check that the key field on both sides of the relationship is set to type 'Text', and please report back your findings. If that solves it, it would be nice to post that so future users might be able to search for and find your solution.

Thanks and good luck.

 

[machtenx]

Hi there Kertusa,

Thanks for the suggestions!

I was able to turn on indexing for those fields without an error, but it didn't seem to help.

Both of the calculations for fullname are text results.

FirstName and LastName are regular text fields.


This doesn't make sense to me because I have an identical relationship that is working fine. The only difference is that this one's purpose is to add new related records, and the other is simply to display existing records.

Any other suggestions you have would be appreciated.

Thanks!

 

[machtenx]

Just figured it out

For some strange reason, I had the idea to make the calculation in the database where new records are created just a plain text field, and it works fine. Ah well, learned something new

 

[kertusa]

Well, now, isn't that interesting. Okay, let's go a little further.

First, I realized that I gave an ambiguous suggestion earlier. I mean to make sure indexing can be enabled for the calculation fields on each side of the relationship, i.e., the FullName field can be indexed. If that field on both sides of the relationship can be indexed, and are both set to 'Text', it really ought to work.

After defining fields and enabling indexing for your key field in 'Invoices' and in 'Customers', try deleting this relationship and re-specifying it. At the time you do that, it may kick up an error message that is more useful than 'not valid'. Or, conversely, it might just work if you define the relationship after the fields have been tweaked. I have had that experience once, where deleting and recreating the relationship gave me a working relationship.

Good luck, I'll be watching this forum off and on for the rest of the day, so please post results!

Thanks

 

[kertusa]

Looks like we crossed paths on posting.

Congrats on fixing the problem. Could you clarify how you've defined the secondary key field (Fullname in Invoices, I believe) just so I understand what the resolution was?

Thank you.