Uniqueness based on 2 fields

[faada]

Hi all,

I am trying to set-up a multi-warehouse inventory system. I haven't used Filemaker in ages and am well rusty.

I have a file with 3 tables - 1)Warehouse Master 2) Item Master and 3)Item/Location

1 and 2 are self-explanatory, 3 will hold the inventory balance of each Item in each Warehouse.

My problem is that I do not want to allow non-unique records based on both Warehouse code and Item code.

I tried setting up a field that auto-entered - WhseCode & ItemCode - and then making it unique, but this did not work.

Any help would be greatly appreciated.

Thanks.

[Reed]

First, you would also need another unique auto enter serial that is unique for each record.

Then,

If you create a self join where both fields are joined with an equijoin, and the unique id is related with the not equal operator, the only related records to any record in the table will have the same values for the two fields.

You could set up field validation for either of the two fields at that point. Just validate by calculation

Count(self_join_table::any_field)<1

When you try to commit a record with the two fields duped, you will get a validation error message.

Let me know if you need a more thorough explanation.

Dana

[faada]

Eureka!!

it works, thanks so much Dana!!

[faada]

btw, I just tried this with only 1 field, a text field that auto-entered WhseCode & ItemCode, did a self join on that field, then validated using the formula you gave me above and voila! Same results!

[faada]

Just realised that this is not working how I want it to, I am not able to modify any field in the record now.

Clearly I need the more thorough explanation that u offered.

[Reed]

Make sure that your self join has the id(not equal)id as part of the relationship so that a record won't match itself. If you don't have that, then you won't be able to edit any records.