Different records from related table

[cruijff]

EDIT: Please refer to post #5

Hi guys, I have to deal with this situation and I'd appreciate your advice:

table:

A (parent_key: aID)

field:

Address

in table B (foreign_key: bID = aID) I want to put 2 different addresses from records of table A.

I'm wondering if I have to put a second parent_key in table A and create a new relationship, i.e.

table A (parent_key1: aID ; parent_key2: a2ID)

table B (foreign_key: bID = aID, foreign_key b2ID = a2ID)

to have in table B:

address1 from table A

address2 from table A

The point is that I need table B to have 2 fields, 1)From: address1 and 2)To: address2 and both address 1 and 2 are stored in table A records.

is there a more elegant solution than having 2 parent keys in table A? Thanks a lot!

[cruijff]

The solution I tried to accomplish obviously won't work. Maybe I need a join table between table A and B?

[efen]

I'm not sure that I understand your explanation correctly but it seems to me that you need a relationship from the primary key of table B to the foreign key of table A to show the 2 addresses in table A

[cruijff]

I'm re-designing one portion of database to make it easier achieving what I want. You're right I'll follow your line...I still have to learn some basics about how to correctly manage relationships (i.e. I'm a noob).

Thanks!

[cruijff]

Sorry for the double topic, I'll contact an admin to remove the previous one asap.

I'd like to have in the table "Orders" two drop down menus (like the one implemented) which fill 2 different addresses. Everything is clearer in the file attached, hope you guys can help me, I started swearing 8 hrs ago

Thanks a lot!

Companies.fp7.zip

[cruijff]

Please delete this topic, i started a new one which should be clearer. Sorry

[Lee Smith]

I'll merge them, but we do not delete post that others have already replied to.

Do not double post. Any changes only require that you clarify in a reply to the original topic.

In the meantime I cant do anything because I'm on the road and using my iPad and it doesn't seem to want to accept my changes, so I have unapproved the other post until get back to my office.

If you have any questions regarding this action, write to me using the Private Topic.

Lee

[cruijff]

Ok this is the problem I'm dealing with: I'd like to have another drop down menu from which I can choose another "company" which completes 3 other fields "Street" "Nation" and "City".

In the attached file there's also a note that should make everything clearer, thanks for the help!

Companies.fp7.zip

[imoree]

Ok this is the problem I'm dealing with: I'd like to have another drop down menu from which I can choose another "company" which completes 3 other fields "Street" "Nation" and "City".

In the attached file there's also a note that should make everything clearer, thanks for the help!

WHY DO THIS??

Confused.

Ship to Vs BillTO ?

[cruijff]

Exactly, shipTo vs billTo, I need 2 addresses on the same table..

[imoree]

Maybe this helps youCompanies2.fp7.zip

[cruijff]

It doesn't really..when I make a selection from the second drop-down menu its 3 fields won't fill accordingly.

I'm working on a script atm I hope it'll do the job

[comment]

Exactly, shipTo vs billTo, I need 2 addresses on the same table..

Two addresses is not the same thing as two companies.

[cruijff]

I know but I'm supposing a company has just 1 address, I don't think it'd be useful to have a 1to1 relationship between companies and addresses. Street/City/Nation can be considered as 3 attributes of a company, right?

[comment]

I don't know: I am only reading what you write. If a company has only one address, then the company's shipping address is the same as its billing address. So it's not clear why do you need to select another company (with another address) for the same order.

[LaRetta]

I'm supposing a company has just 1 address

That is not a safe assumption under normal situations. Companies frequently bill to PO box or headquarters, with shipping going to street or warehouse location. Why don't you have addresses as a related table? And even you realize they can be different - that is why Orders normally have a BillTo and a ShipTo.

And as asked, why would you select a different company for an Order? You don't have the same company entered twice in the Companies table do you? Please don't use script - that would be absolutely wrong way to go here.

[cruijff]

I need to make a "purchase orders" table and be able to insert the Consignee address and the Delivery address. These addresses can be the same (same company) or different, which means that the order consignee has to ship to the delivery address( a different company).

In my case a company has just 1 address, thou I can upgrade my db to have an address table, displaying with a portal on "companies" all their related addresses, but the problem remains the same.

[cruijff]

Why should I avoid a script?

I was thinking about a drop-down menu with a value list of companies ID, a script that reads the value, stores it in a local variable, performs a find, fetches the strings of Street/Nation/City fields in "companies" and copies them in Street/Nation/City on the orders table.

[comment]

In my case a company has just 1 address, thou I can upgrade my db to have an address table, displaying with a portal on "companies" all their related addresses, but the problem remains the same.

The problem is not exactly the same. In the case of one company having two addresses, you select only one company for the order. Then you either select from among the company's addresses or the addresses are automatically selected by their type.

OTOH, If every company has only one address, then you must select two companies for the order - i.e. your order needs to have a ConsigneeCompanyID field and a DeliveryCompanyID field - and there must be two relationships between Orders and Companies.

Why should I avoid a script?

I was thinking about a drop-down menu with a value list of companies ID, a script that reads the value, stores it in a local variable, performs a find, fetches the strings of Street/Nation/City fields in "companies" and copies them in Street/Nation/City on the orders table.

Because a lookup does all of that without the extra work and without depending on a script having run successfully.

[cruijff]

Thanks comment. The point is that consignee address and delivery address can be related to 2 different companies. Not that I treat a company and its warehouse as two different companies.

From what you wrote I assume I'll have to create a ConsigneecompanyID and a DeliverycompanyID fields and relate both to companyID (parent key on Companies)?

This way I'll have a relationship as:

companyID = ConsigneecompanyID

AND

companyID=DeliverycompanyID

That "AND" doesn't sound good to me..maybe AND/OR is the right one to go for? EDIT: What the hell did I write here..I mean I should work on the relationship (=, !=, > etc.) maybe

[cruijff]

companyID = ConsigneecompanyID AND companyID=DeliverycompanyID

This way when trying to set up a lookup field for Street_consignee (displaying Companies::Street) the message I read is:

When a new entry is made in ANY of the relationship fields in this table, this lookup will copy the value from the first matching related record in the table "Companies".

I'm really frustrated..this way Orders::street_consignee and Orders::street_delivery will display a value (the same) only when I I choose from the drop down menu the same company..

[comment]

Try it this way?

Companies2.zip

[LaRetta]

Why should I avoid a script?

Because in all of FileMaker, you should let the natural relationships handle things instead of horsing with script. Script should only be used when no other option is available.

Here's an example of how to do it.

Mine didn't take into account that you might want to preserve the addresses in the order. Use Michael's - his post wasn't there when I originally responded (seems we posted simultaneously). :laugh2:

Companies3.zip

[cruijff]

Thanks a lot guys! What I need is another table occurrence for "Companies" am I right?

THANKS :)

[LaRetta]

Yep! One company table - two occurrences in the graph

[cruijff]

Ok guys, you cannot imagine how much I learnt from this topic, especially comparing your 2 solutions. Table Occurrences ftw.

[imoree]

i learned a lot too. Thanks COmment & LaRetta. : )

:grad: