validate order number

[smillerdad]

I am trying to confirm that a customer# and order# entered in one database match for the same customer# in another database (created only for this verification) containing customer# and order#. My relationship and calculation work fine as long as the order# entered is the first one found in file 2 for that customer#, but if that customer# has multiple records with more order#s my validation calculation fails with all but the first. I need to continue to check for all order#s for that customer#.

I then tried copying all order#s for a customer into a single record with a repeating field for order# and used extend in the calculation but still could verify only the first order# in the repeating field.

Any guidance would be appreciated. Thanks,

Steve

[CobaltSky]

Hi Steve,

The answer to your problem lies in devising a means to retrieve all of the order numbers from the related file, then running a check against them using the PatternCount( ) function.

First, to retrieve all the order numbers at once, I suggest that you:

1. Create a relationship that linke the files based on a match between customer# fields in both files.

2. Create a value list called CustomerOrders which uses 'Only related values' via the relationship referred to in 1 above.

With this in place you will then be able to validate the order number on the current record with the formula:

PatternCount(ValueListItems(Status(CurrentFileName), "CustomerOrders"), Order#)

...which will return 1 if the order is listed in the related file and 0 if it is not.