Problem with Calculated fields in relationships

[Scott Pon]

I have a confusing and fustrating problem.

I have a Sales Order file and a Purchase order file.

In the Purchase order file, the customer service picks a customer name (ex. Intel) that has a customer code (Int001). Then enters the Purchase Order Number (54322). I have a calculating field, named KEY that concatinates the Customer Code with PurchaseorderNumber (Int001-54322), it produces the Unique Key. In addition there is a Purchase Order Value, stating how much money is on the Purcahse order.

In the sales order file, much like the Purhchase Order Files has a customer name that has a customer code and a Purchase Order Number. From there it is calculating a Key, but may not be unique, since a Purchase Order may be used for multiple orders.

I have a relationship between the 2 KEYS. In the purchase order file, I have a portal showing every sales order that used the Purchase Order. This also calcuates the remaining Money left on the purchase order.

My problem comes when I have a 2nd customer (AMD) that gives the same Purchase Order Number (54322). Then AMD places a Sales Order.

For some reason, in the Portal for Int001-54322, I see the AMD Sales Order in the list. THat's not supposed to happen!!! A real problem occurs when problem order uses up that PO number's money (only in the database, not in reality).

Im wondering if there is a bug in FM5? or if someone else has experienced this problem before? Or if there are any ideas why this is happening. If you need more details I will be glad to explain more.

Thanks for viewing!

[QuinTech]

I have a relationship between the 2 KEYS.

Does this mean you have two relationships, or have you created a concatenated key? If you're used to working in other database programs, i wonder if maybe you have two relationships.

In version 6 and prior, the only way to make a relationship based on two criteria is to make a calc field concatenating those two fields on both sides of the relationship. If you have a relationship between City and City, and another between State and State, then entering Pittsburgh on the left side of the relationship matches Pittsburgh, PA and Pittsburgh, KS (forgive the misspelling). Also, PA on the left side matches Pittsburgh, Philadelphia, Harrisburg, ...

FM7 handles this much better, but in v6, you must create a calc field:

City & " " & State

and match that to a similar field in the other file.

Does this help solve your problem?

Jerry

[Scott Pon]

I have created the key to look like this:

Key is a Calculated Field of: Customer Code & "-" & Purchase order Number

There is only 1 relationship from the Purchase Order Key (defined above) to the Sales Order Key. There is no relationship the other way.

Do you think the "-" could be causing problems in the relationship?

Thanks again.

[QuinTech]

I don't think so... it works for me. Are you sure that both key fields are calculating correctly? Take a look at that AMD record and see if it actually shows "AMD001-54322" in the key field. I see no reason why your setup should not work.

J

[Scott Pon]

Here is what I've done so far...

I Changed the relationship twice for testing:

First removed the CustomerCode, so any order with the PO number should appear. It seems to be working

Then I tried it with only the CustomerCode, so all their orders for the customer appears. It seems to be working here too.

Here's another possiblity. do you think putting a space (" ") in the code will effect something???

The codes actually look like this:

it would look like "AMD -CA01-654322" (two spaces) and "Intel-CA01-54322"

[QuinTech]

I think maybe your calculated keys are set to calculate as numbers. That would be one reason for this issue. When FM indexes number fields, it ignores everything but the ten digits, so "AMD-CA01-654322" and "Intel-CA01-54322" both look like "01654322". That's probably why those are matching. Kick me for not seeing it sooner.

J

[Scott Pon]

I think maybe your calculated keys are set to calculate as numbers. That would be one reason for this issue. When FM indexes number fields, it ignores everything but the ten digits, so "AMD-CA01-654322" and "Intel-CA01-54322" both look like "01654322". That's probably why those are matching. Kick me for not seeing it sooner.

J

MAJOR PROPS TO THE MAN! That did it! that solved my problem! no more mistakes! no more non-related POs! Welcome correct calculations!

You're the man! Thanks for all your help!

I'll pour you a beer!

[QuinTech]

Phoenix, Sunday night, 9pm. I like Labatt's.

[Scott Pon]

Another issue came up with this! (ahhhhhhhh!) One of the employees noticed that if the PO Number has a "." (period) in it, the same issue occurs. Its hard to explain so I'll give the example.

Company AMD

1st PO: 123.30 --> CustomerPOKey (calcuated Text) is AMD-123.30

2nd PO: 123.31 --> CustomerPOKey (calcuated Text) is AMD-123.31

Sales Order 10 references 1st PO 123.30

Sales Order 11 references 2nd PO 123.31

However when I look at the PO 123.30 it shows both Sales Order 10 and 11. It should only show sales order 10.

Any ideas? Thanks in advance.

[-Queue-]

What's the relationship from PO to Sales Order?

[QuinTech]

Can you post the files (or clones) here?

[Scott Pon]

Issue resolved. It turns out that the String Comparing in a word limited to 20 characters. And I have a single word over 20 characters. But now that I put a space in the word (makeing 2 words) it now works.

Thanks to FMForums!