Many to Many too Many?

[allexv]

I'm having a hard time working out these relationships on paper. I have an order (table) with many line items (table) and multiple ship-tos (table). Some line items will ship out at different times to different recipients. How do I establish that relationship, so that any order shows multiple recipients receiving 1 or more of the line items?

 

For example, I receive an order from McDonald Corporation for 10K straws, 2K cups and 4K napkins. They want me to ship to 10 distribution centers at different dates different combinations of cups, napkins and/or straws. How will this show up on one order? How do I use portals to accomplish this? Or do I use some other technique, subsummarizing, etc.?

 

To top it off, each line item has many fields. And Portals only allow me one line of information. How do I see more of the fields in line items through a portal? Or am I totally off? Or am I limited to one recipient per order?

 

Any help would be greatly appreciated. I'm at wit's end and can't really finish my project without this step.

 

Thanks again,

Allex

[Maarten Witberg]

How will this show up on one order? How do I use portals to accomplish this? Or do I use some other technique, subsummarizing, etc.?

Hi,

 

Probably both. A portal for data entry (and online viewing) and a printed statement of invoice (or goods delivered, for that matter) using the subsummary technique.

 

 

How do I establish that relationship, so that any order shows multiple recipients receiving 1 or more of the line items?

 

assuming the smallest unit is a line item for one product to one location on one shipping date, it could look something like this, but I have not made a test.

 

 

 

 

And Portals only allow me one line of information.

You can click-drag the corner handles of a portal to enlarge its size.

 

 

 

kjoe

 

 

PS the order items table needs its own OrderItemID field of course, but it is not used in any of the relations in the above schematic

[allexv]

Thanks for the help. I have a question, though. Wouldn't your diagram show a circular reference? Do I need another table occurence to accomplish those relationships?

 

http://web.mac.com/allexv/iWeb/FamilySite/FileMaker_files/ER_Diagram.jpg

 

 

What I am looking to accomplish is something like this:

http://web.mac.com/allexv/iWeb/FamilySite/FileMaker_files/OrderEntry.jpg

 

 

I'm not sure if FileMaker can do this, though. What do you think?

 

 

Again, thanks for your input.

-Allex

[Maarten Witberg]

Ah but it's not a TO graph. When defining the relationship, Filemaker will automatically indicate if a new TO is needed. It will however, be the same table.

 

As for the second image: I think it can be done, but not using a portal for the recipient info items, as you can't show a portal in a portal. What you probably can do is make a list view for recipient records, with order and client info in the header. The left sidebar is probably out in that case.

 

If you insist on using the orders layout at the center, then the cascading portals technique as demo'ed by Ray Cologon may be a good alternative (I just keep on pointing people to it ). But it will only give you the order line items for one recipient at a time.

 

kjoe

[Maarten Witberg]

Sorry, I completely forgot that there's no table defined for orders per recipient, so you can't show individual records of them. Not, at least, in your current setup.

 

kjoe