Hi guys, I'm building a table for Purchasing Orders, and I have a problem.

I have a field in "Purchasing Orders" table named "Supplier name" set up as a drop down menu linked to a value list obtained from the table "Suppliers".

In the "Suppliers" table I have "street" "city" and "state" fields.

In "Purchasing Orders" I also have 3 fields named "Street", "City" and "State".

I would like Purchasing Orders' fields (street, city, state) to be auto-completed when I choose the wanted "Supplier name" from the drop down menu.

Do I need a script (or calculated fields) to achieve this or is there a way I can make it without it? (The 2 tables are obviously related via serial keys)

I have the suspect I'm doing a dumb question, but I'm a noob and slightly tired atm, please help.

Sorry for my poor english, hope everything is clear..thanks!

Hi cruijff,

Your english is fine!

If the tables are related then there is no need to enter the data again in Purchase Orders. You can just place your Suppliers fields directly onto the Purchase Order.

However, if you must implant the data into the purchase order as static data (for audit purposes) then you can set those Purchase Order fields as Lookup or Auto-Enter. This would only be necessary if it was important to track what the Supplier address was at the time the Purchase Order was created. That is usually important only for things such as what was an address at the time of shipment or similar requirements.

OK..

I have a field in "Purchasing Orders" table named "Supplier name" set up as a drop down menu linked to a value list obtained from the table "Suppliers".

In the "Suppliers" table I have "street" "city" and "state" fields.

In "Purchasing Orders" I also have 3 fields named "Street", "City" and "State".

In your layout add your "RELATED DATA" such as

Suppliers::SupplierName

Suppliers::City

Suppliers::State

YOu don't need duplicate data, just related data.

If you have a valid relationship and "ALLOW Related Table to CREATE" you should see the data in your layout.

Hope that helps.

-ian

Hi Ian, just to clarify ...

If you have a valid relationship and "ALLOW Related Table to CREATE" you should see the data in your layout.

Allow Creation of Related doesn't need to be checked to see related data. I think you know that - I just wanted to clarify. :^)

You're right, just a minute ago I asked myself how much stupid I am. The point is that in my suppliers table I have a portal displaying supplier addresses since they can be more than one per supplier. I simplified my question to get an immediate answer and I ended up asking the wrong one...

Thanks for the help anyway, I'll try to figure out a solution bearing in mind what you guys wrote

Maybe I could create a contextual value list of addresses based on the supplier names value list, and then putting addresses table's related fields in my Purchasing Order table!

Can any of the supplier addresses be chosen? If there is one preferred address for a Supplier, it is normally handled by having a field in Suppliers called PreferredAddressID which holds the AddressID of the chosen address. Then the lookup would pull through from the Addresses table instead (where it is joined to Suppliers as: Suppliers::PreferredAddressID = Addresses::AddressID.

If the User must select the address of the supplier, you can use conditional value list after User selects the Supplier to then select their address. I can't quite visualize your setup to be more specific.

Hi Ian, just to clarify ...

Allow Creation of Related doesn't need to be checked to see related data. I think you know that - I just wanted to clarify. :^)

But he may want to create the supplier via the Purchase Orders Layout is what i was thinking>!

I'll try to be as clear as possible.

Tables (obviously related among them):

Suppliers addresses

Suppliers

Purchasing orders

On the Suppliers table I have a portal which displays (and allows data enter of) suppliers' addresses. So 1-to-many relationship between suppliers(1) and addresses(many).

On purchasing order I'll have a drop down menu of supplier's names. So I have a supplier's name value list. I'll try to create a contextual value list of addresses related to suppliers' names and then I plan to put Suppliers addresses::street (state/city) on Purchasing orders table.

I hope this will work, thou i'll have to think a bit more about this.

I'll try to be as clear as possible.

Tables (obviously related among them):

Suppliers addresses

Suppliers

Purchasing orders

On the Suppliers table I have a portal which displays (and allows data enter of) suppliers' addresses. So 1-to-many relationship between suppliers(1) and addresses(many). ( WHY -- addresses should only be a field in the Suppliers Table! )

On purchasing order I'll have a drop down menu of supplier's names. So I have a supplier's name value list. ( you only need to create the VL using the Suppliiers Table!)

I'll try to create a contextual value list of addresses related to suppliers' names and then I plan to put Suppliers addresses::street (state/city) on Purchasing orders table. ( WHY AGAIN )

If you want, you need a Bill to: and a SHIP to: Field( you can see how this is done in FMP sample invoice database.)

I hope this will work, thou i'll have to think a bit more about this.

Ian? Did you plan to say something here?