Relationship - Need Two Fields

[Jess Girard]

Hi:

Stupid Newbie Question, no doubt, but I give up....

We make custom products. We refer to what we produce by our P/N's, and our customers use their own P/N's which must appear on packing lists, invoices, barcode labels, carton labels, etc. Often, however, a customer assigns a subcontractor to make assemblies for them. So now instead of customer "A" we have customer "B" for the part.

So now customer "B" purchases our part. Great! But customer B calls our part a different P/N. So we need to have our "orders" table access not only the customer's P/N to cross-reference my P/N, but we need a special lookup which returns customer "B's" part number when they order it. So I need to obtain a relationship or lookup linked not only to my P/N, but unique customer number.

Presumably this is simple. But not for me since I'm new with FM7 Pro. Any suggestions would be appreciated. Thanks in advance!

Jess

[Jess Girard]

Hi:

Stupid Newbie Question, no doubt, but I give up....

We make custom products. We refer to what we produce by our P/N's, and our customers use their own P/N's which must appear on packing lists, invoices, barcode labels, carton labels, etc. Often, however, a customer assigns a subcontractor to make assemblies for them. So now instead of customer "A" we have customer "B" for the part.

So now customer "B" purchases our part. Great! But customer B calls our part a different P/N. So we need to have our "orders" table access not only the customer's P/N to cross-reference my P/N, but we need a special lookup which returns customer "B's" part number when they order it. So I need to obtain a relationship or lookup linked not only to my P/N, but unique customer number.

Presumably this is simple. But not for me since I'm new with FM7 Pro. Any suggestions would be appreciated. Thanks in advance!

Jess

[Jess Girard]

Hi:

Stupid Newbie Question, no doubt, but I give up....

We make custom products. We refer to what we produce by our P/N's, and our customers use their own P/N's which must appear on packing lists, invoices, barcode labels, carton labels, etc. Often, however, a customer assigns a subcontractor to make assemblies for them. So now instead of customer "A" we have customer "B" for the part.

So now customer "B" purchases our part. Great! But customer B calls our part a different P/N. So we need to have our "orders" table access not only the customer's P/N to cross-reference my P/N, but we need a special lookup which returns customer "B's" part number when they order it. So I need to obtain a relationship or lookup linked not only to my P/N, but unique customer number.

Presumably this is simple. But not for me since I'm new with FM7 Pro. Any suggestions would be appreciated. Thanks in advance!

Jess

[Vaughan]

Nothing about this sounds simple to me!

It sounds like you're going to have to make tables of data that link the different numbers together. Once you have that data it may be easy to pull the right numbers out.

[Vaughan]

Nothing about this sounds simple to me!

It sounds like you're going to have to make tables of data that link the different numbers together. Once you have that data it may be easy to pull the right numbers out.

[Vaughan]

Nothing about this sounds simple to me!

It sounds like you're going to have to make tables of data that link the different numbers together. Once you have that data it may be easy to pull the right numbers out.

[Jess Girard]

Hi Vaughn:

Thanks for your input.

I've had one idea thus far, i.e., to create (hidden, I suppose) fields in our "orders" and "parts" tables which are an amalgam of customer number & our part number.

Then we can do a lookup from within the order table for that combination and grab the customer P/N field from the "parts" table which satisfies it. Seems a kludgy solution but I think it will work.

[Jess Girard]

Hi Vaughn:

Thanks for your input.

I've had one idea thus far, i.e., to create (hidden, I suppose) fields in our "orders" and "parts" tables which are an amalgam of customer number & our part number.

Then we can do a lookup from within the order table for that combination and grab the customer P/N field from the "parts" table which satisfies it. Seems a kludgy solution but I think it will work.

[Jess Girard]

Hi Vaughn:

Thanks for your input.

I've had one idea thus far, i.e., to create (hidden, I suppose) fields in our "orders" and "parts" tables which are an amalgam of customer number & our part number.

Then we can do a lookup from within the order table for that combination and grab the customer P/N field from the "parts" table which satisfies it. Seems a kludgy solution but I think it will work.

[Brian C]

If I understand this correctly:

You have a P/N for an item.

Customer A has a P/N for the same item.

Customer B has a P/N for the same item.

All 3 P/Ns are different from one another but all 3 refer to the same item. Correct?

Lets assume you have a Parts Table which contains the items that have your P/N associated with them.

Create a new "Linking" table which will store your P/N and the P/N for a specific customer.

EXAMPLE:

Parts Table fields:

PN (your internal P/N)

Description (item description)

New "Linking" Table fields:

PN (your internal P/N)

CustomerID (this will store the customer identifier of: A, B, C, D, etc)

CustomerPN (the P/N for the particular item)

c_LINK (calculation field combining your PN with the CustomerID)

Now just add the same c_LINK calculation field to any of your other tables to combine your internal P/N with the Customer ID. You can then create a relationship from your table to the Linking table using this calculation. This will allow you to return the correct P/N for that particular customer for a given item.

[Brian C]

If I understand this correctly:

You have a P/N for an item.

Customer A has a P/N for the same item.

Customer B has a P/N for the same item.

All 3 P/Ns are different from one another but all 3 refer to the same item. Correct?

Lets assume you have a Parts Table which contains the items that have your P/N associated with them.

Create a new "Linking" table which will store your P/N and the P/N for a specific customer.

EXAMPLE:

Parts Table fields:

PN (your internal P/N)

Description (item description)

New "Linking" Table fields:

PN (your internal P/N)

CustomerID (this will store the customer identifier of: A, B, C, D, etc)

CustomerPN (the P/N for the particular item)

c_LINK (calculation field combining your PN with the CustomerID)

Now just add the same c_LINK calculation field to any of your other tables to combine your internal P/N with the Customer ID. You can then create a relationship from your table to the Linking table using this calculation. This will allow you to return the correct P/N for that particular customer for a given item.

[Brian C]

If I understand this correctly:

You have a P/N for an item.

Customer A has a P/N for the same item.

Customer B has a P/N for the same item.

All 3 P/Ns are different from one another but all 3 refer to the same item. Correct?

Lets assume you have a Parts Table which contains the items that have your P/N associated with them.

Create a new "Linking" table which will store your P/N and the P/N for a specific customer.

EXAMPLE:

Parts Table fields:

PN (your internal P/N)

Description (item description)

New "Linking" Table fields:

PN (your internal P/N)

CustomerID (this will store the customer identifier of: A, B, C, D, etc)

CustomerPN (the P/N for the particular item)

c_LINK (calculation field combining your PN with the CustomerID)

Now just add the same c_LINK calculation field to any of your other tables to combine your internal P/N with the Customer ID. You can then create a relationship from your table to the Linking table using this calculation. This will allow you to return the correct P/N for that particular customer for a given item.

[Jess Girard]

Brian, thanks for your reply. I'll try what you suggest and let you know what I'm able to accomplish.

[Jess Girard]

Brian, thanks for your reply. I'll try what you suggest and let you know what I'm able to accomplish.

[Jess Girard]

Brian, thanks for your reply. I'll try what you suggest and let you know what I'm able to accomplish.