Relationship HELP !!! I start feel crazy

[guy tanguay]

Hi

I have one Table call "Parts" and another call "elements".

I need many differents "elements" to acheive a "Parts".

So, every "Parts" records have is unique "Parts number" field .

Same thing for each "element", they have there "parts number" field wich they are related with.

Every "elements" need to have a "materiel type" field fill-up 2 choices from a VL: plastic or metal

Here the core of my problem:

I did create 2 new calculations field in the "elements" table one call "_plastic" wich = "plastic" and the other call "_metal" wich = "metal"

I did also create 2 others Graph of Table "elements" with a relationship to is TO with 2 creteria : "Parts number" and "materiel Type" :

So, one occurence to the graph is: Parts number=Parts number and materiel Type=_plastic

and the other is occurence is Parts number=Parts number and materiel Type=_metal

After that, I go back to my TO-Graph "elements" and make a relationship with "parts" like this :

Parts number=Parts number.

OK, them a create a portal call "plastic listing" and another one call "metal listing"

"Plastic listing- Portal" is meant to have all the "element" related to a "parts" that have, firts the same "Parts number" and second the materiel type need to be plastic

So, in the portal setup i have selected "show related records from : elements_plastic_filter" (the occurence in the graph that have a filter for plastic)

I do the same with "metal listing portal"

All that work perfectly, until i got 2 elements that have the same "parts number", them it appear twice in both portal.

SO HELP ME, whats wrong other them me being dumb ?

thx

guy

[Sxeptomaniac]

Not dumb, just inexperienced, it seems (no shame in that). It's a problem in my earlier work that I've had to deal with, too.

Your relationship is suffering because the "Parts number" is used for the relationship, but you have a duplicate part number.

I've learned that you want to create your primary relationships based on a field with an auto-entered serial number, and not real-world data. Otherwise you have problems like this one, where a number is duplicated. You could then use one of three primary methods to have that serial number entered into your elements database:

1) a pop-up list in one of your other fields and look-ups to fill in the correct serial number in your elements database (but you might still run into problems with duplicates here, unless it's done carefully).

2) use a portal on your parts layout to enter the data.

3) script creating a new element record to make sure the correct serial is entered.

If you still feel you need to continue to do it the current way, you will need to set "Parts number" as unique, and figure a way to adjust one of the duplicates to be different from the other.

[guy tanguay]

Hi,

Tanks for answers, but i have questions / precision:

In my Parts table the "Parts number" is ALWAYS different for each record and Always Unique (but i didn't click a specific option/buttom to make it "Unique". I need that number to be entered manually because it use is specific naming convention.

But, each "elements" records need to be attach or link to a "parts", so i figured that "parts Numbers" was perfect . So each elements is related to a "parts". And it work great.

The problem, is that i need to have two portal of "elements" in the same Layout in "Parts"

Those portal need to be determine by another field : Like Catgory 1 and Category 2

Any thought ?

guy

[Sxeptomaniac]

I threw together a test file to see if maybe I have a better idea of what you are trying to do. Take a look at it and see if this is doing approximately what you want.

Parts.zip

[guy tanguay]

Yes that's it

Thanks !

I did implemant that in my file and it work perfectly.

Originally,I didn't have the boolean thing, now i do !! But i changed the checkbox buttoms for a pop-up menu.

Also, i didn't make a partSerial number field , but i did put my part_number unique (always validate)

And i did add a element_serial_number.

Also, when you enter a part_number in "element" i made the number to be validated using a "Value list" that store all the part_number from table "part".

So far, it looks to work perfectly.

I learned a lot with validation , unique field and relationship.

Thanks again for your help and feel free to give me some other awsome advices .

guy

[Sxeptomaniac]

You're welcome.

I recommend continuing to read through postings here. I've learned a lot just by seeing the answers to other peoples' problems.