Hi all,

been lurking here for a bit, haven't been doing much in Filemaker apart from maintaining my v5 databases making sure they run on 8.

I've now decided to port some stuff to 8 and have run into a brick wall. For the life of me I can figure out how to set up relationships in 8 to do what I want. I've tried setting them up the way they in v5 but had no luck, I'm now starting to question my sanity.

I'd love for someone to have a look my files and tell me where I'm going wrong. What I have is a set of three (stripped down) files, one Suppliers, one Stock details and one Purchase orders. In purchase orders, once a Supplier has been selected only the items available from that supplier show in the item pop up. The recreate file is my fruitless effort of re doing this in one file. While it's true that I can just keep on using the old files I'd love to have this in just one database, moreover, I'd like to understand where I went wrong in my efforts. I've tried so many different ways that I lost count of what is going on.

The files are here: deleted, see new file below

I appreciate any help.

cheers

michael

Edited March 10, 200619 yr by Guest

I've replaced the file above with a less broken one...

Well, I managed to do it, but not really understanding why and how.

Bought the Bible and the Missing Manual yesterday, looks like I'll have some studying to do.

I'd really appreciate if anyone could have a look at the new working file and let me know how this could be improved in terms of relationships.

thanks

New_Purchase_Orders.fp7.zip

Edited March 10, 200619 yr by Guest

Hi michael,

I noticed right away that you seem to be missing a line item table. You're using "Item 1", "Item 2", etc., which will give you problems down the road. Whenever you have a situation where you need to have several items of the same type (especially when the max number of these is unknown,) it is a good candidate for a related table.

Attached is an ER diagram showing a typical PO structure. Note that an ER diagram is not the same as FileMaker's relationship graph. The underlined field names are the primary keys. The foreign keys are implied by the relationship lines.

For FileMaker's relationship graph, you would need two occurences of one of the tables because a loop is not allowed. I would probably make two instances of the Vendor table, one related to PO by Vendor ID, and one related to Product by Vendor ID. To make the value list of products for the selected Vendor, you would then need an additional table occurence of Product off the Vendor by PO table occurence.

Hi Ender,

thanks for the reply.

I've tried setting up a new file with the info you sent but failed to get it work.

Since the number of line items is limited in this case I might stick with what I've got working now and leave your solution for a later date when my head is a little clearer.

thanks

michael

"Since the number of line items is limited in this case I might stick with what I've got working now..."

Bad idea Michael. It'll end in tears.

I've actually had the older set of files running for a number of years and for the purpose they worked perfectly...

To make the value list of products for the selected Vendor, you would then need an additional table occurence of Product off the Vendor by PO table occurence.

I think I got the first part right, see file, but am totally confused by the last part.

Also, where do I get my line items from in the PO file?

I'm trying...

michael

fmforums_ender.fp7.zip

Try pulling apart this.

PO.fp7.zip

Try pulling apart this.

Cool, makes sense now.

I've added a calc to give me a total per line, where would I get a total for all the line items from?

Thanks

michael

PO.fp7.zip

I just noticed, if create a vendor and some products for that vendor I get "value list not defined" in the Order. Why is it doing that?

Define a calc in the PO table:

Total (calculation, number result) = sum(PO_Line_Item::Extended_Price)

Define a calc in the PO table:

Total (calculation, number result) = sum(PO_Line_Item::Extended_Price)

Thank you.