Validate unique in related records

[RDWirr]

I have some related tables of categories of items that each have an item number. So there are tables Category1 and Category2. The Item Number in Category2 can be repeated in the whole set of records but I need to have a way of keeping the item number unique within the set of records related to each Item in Category1. In other words table Category1 has items 1, 2, 3,etc it is related to table Category2 with items 1:1, 1:2, 1:3 and 2:1, 2:2, 2:3, etc. The relationship, keyed together with auto entered P&F, keys works fine and I can display only the related items in a portal through the relationship of tables Category1::Category2. The item numbers are not the P&F keys and are manually entered. The thing is when I create a new record in Category2, I need to have unique Item Numbers within the records related to each Item Number from Category1. I have been trying to do this with with a calculated validation but I am missing something. Can anyone steer me in the right direction?

Thanks in advance,

RD Wirr

[comment]

I am not sure I am following this fully, but try creating a new Text field in the Category2 table and set it to auto-enter a calculated value (replacing existing value) =

Category1ID & " | " & ItemNumber

Validate this field as unique.

[bcooney]

"So there are tables Category1 and Category2"

I stopped there. You should have a table, Categories, and a record for each type.

Then, you have a table, Items. Each item is assigned a CategoryID.

Can an Item be assigned more than one Category?

[comment]

I *think* "Category1" is Categories and "Category2" is Subcategories.

[RDWirr]

I'll try this, Maybe I can just use the item numbers already entered in Category1 and concatenate it with the Category2 entry to validate. Thanks, I'll have to work on this tomorrow.

RDW

[RDWirr]

Yes you could call them by those titles, but the logic is the same either way. In this solution there are actually three levels of categories and 10 Attributes that come after that. There is a 3 digit category number from the combined numbers of the three categories and then a generic 5 digit serial number after that that identifies individual items. This is a system for keeping parts and materials organized with Part Numbers and Naming Conventions. We have to have a way to organize by categories and attributes so everyone here is using the same names and identifiers. Otherwise we have the tower of Bable effect.

Rgds,

RDW

[RDWirr]

Yes, there are tables Category1 and Category2. The relationship between Category1 and Category2 is a one to many, respectively. I have called the individual records in the two category tables, "items" for lack of a better term but maybe this is confusing. They are indeed Category and SubCategory and even a SubSubCategory and this is all before we come to the actual items which is further down the chain of relationships. This query is just concerned with getting the categories organized before we come to the actual items. We have 10's of 1000's of items and variations and need a lot of different categories.

[bcooney]

Thanks for clarifying. Sounds similar to a system I've worked on which had EquipCat and each equipcat record had a combo of three different subcat fields. The EquipCatID was them assigned to Equipment records. The user could search thru the equipcat relationship on any of the three subcats.

I believe we validated for duplicate combos using a concat calc, but today I'd use a script trigger on record commit.

[RDWirr]

Hello Comment, I tried doing a version of your suggestion but basically, as I understand it, this means concatenating the item numbers (category numbers) for Cat1 and Cat2 related records and then validating that against the entire range of Cat1&Cat2 item numbers. This is not working because I think it is validating against itself. In effect =Cat1::Cat1ID & Cat2::Cat2ID <> Cat1::Cat1ID & Cat2::Cat2ID. As soon as I commit the new record with the the new Cat2ID it is not unique anymore. I think I must be missing something here. Is there some way to validate before the record is committed?

[RDWirr]

Hello BCooney, Thanks for the comments. Yes, this is probably something similar to your earlier project. This one is set up as a search by drop-down CVL. Have a long row of drop down lists that successively narrow the list of items that are displayed in a portal.

Your comment about the script trigger is interesting. Can you elaborate? I already tried Comment's suggestion of validating the concatenated item numbers but ran into problems with that. Seems like I have to validate before the record is committed. After it is committed it becomes part of the list I am validating against.

[comment]

I think we have a problem with the vocabulary here. Let me use my own terms and then see if you can translate it to yours:

Table Categories:

• CategoryID - Number, Auto-enter serial number

• Category - Text, Unique

Table Subcategories:

• SubcategoryID - Number, Auto-enter serial number

• CategoryID - Number (foreign key for Categories)

• Subcategory - Text

• Validator - Text, Auto-enter calculation, Unique

The auto-entered value for the Validator field would be =

CategoryID & " | " & Subcategory

[bcooney]

One way is to Find for the concatenated key and see if Get(foundcount)>1. If so, delete the "new" record.

We had four fields combine to form a Equip Category record. We didn't help the user build logical combinations. If they entered a combo that didn't already exist, it was valid.

[RDWirr]

Yep, that worked perfectly. Sorry I was confused at first. I think maybe you meant 'Category & " | " & Subcategory' instead of 'CategoryID & " | " & Subcategory'? I was using the equivalent to Category - Text as part of my Category identifier rather than the Parent Key.

Anyway, that works very well. Thanks for your help Comment.

Now I just need to find an easy way to populate my validate field with all those auto-enter values. A looping script of some kind I would think is the solution for this.

Regards,

RD

[RDWirr]

Hello BCooney, This is an interesting solution. I think it means using a script to find and validate, triggered by committing the record, right? For this current issue I will use Comment's solution because it is so simple and script-less but I will play with your concept for a different issue i am anticipating. Thanks very much for your help.

Regards,

RDW

[comment]

Change the Validator field's type to Calculation (same formula, result is Text, stored). Click OK to exit Manage Database. Then change it back to a Text field again. This will populate the existing records.

[RDWirr]

That's brilliant Comment! What a nice trick. You just saved me a half a day of playing around with scripts to accomplish this.

Thank you very much,

RDW

[RDWirr]

But, but...now that I try it, I see that I cannot store the calculated results because the calculation is referencing a related field. Is there any way around that?

Regards,

RDW

[comment]

The calculation should not be referencing any related fields. CategoryID is a field in the Subcategories table (the foreign key to the Categories table) and so is Subcategory.

[RDWirr]

Yes, but in my actual solution, the number I am trying to validate is a concatenation of manually entered category numbers, one from Categories and one from Subcategories. I can't use the CategoriesID, parent key from Categories as part of the validation field because we need to be able to change this number after the record has been created. It provides the logical sorting order. As mentioned earlier in this thread, I actually have three levels of Categories and after the first Category level the numbers are no longer unique but only have to be unique within the records related to it. So I am just grabbing the first and second Category numbers from the related records to calculate the Validate field lookup.

Thanks,

RDW

[comment]

I am afraid you have lost me. I think that the "manually entered category numbers" can be anything - as long as the string is unique within the parent category. Validating the combination of foreign key & string as unique achieves exactly that. If you change the string, validation will occur again. Why won't this work for you?

[RDWirr]

Hi Comment, thanks very much for sticking with me on this issue. I think maybe I have been wearing my hat too tightly or maybe just at it too late. I now understand your logic, I tried it and it works fine...for both the Validation and populating the Validation field.

Thanks again,

RDW