Basic Relational Problems

[bob sherman]

I have created a Requisition File, Product File and Purchase Order File. The Requisition File is related to the Product File by a product code field. The Requisition File is related to the Purchase Order File by the P.O. number field. The user can create a requisition for a supply item by pasting the product code into the requisition file product code field. This will automatically displays the product name, description and cost.

The user next creates a Purchase Order and a PO number which, when pasted in the Requisition File P.O. Field next to each supply request, will automatically display those items on the PO form in the related PO File by way of a portal. Well, almost. The relationship of the portal is with the Requisition File so only the product code from that file displays. The remaining fields from the Product File do not display.

Can someone set me straight on these relationships? Thanks.

[djgogi]

Let see if I have understood the situation.

The set of products associated to Requsition File is exactaly the same as the one for PO ?

Also there is one to one relationship from Requisition file to PO file?

If so than you need an associative (join) table:

req_prod(reqID, prodID)

'cause you need to solve many to many relation which exists between Req and prod file.

So you end up having these tables:

req_prod(reqID, prodID)

requisition(reqID pk)

products(prodID pk)

order(po pk, reqID fk)

Make an relationship from order to req_prod reqID-->reqID

If my assumptions were wrong than post more info

HTH

Dj

[Korky]

FM Pro has only one level of relationship. Your relationship heirarchy appears to be Purchases-Requisitions-Products. The top level, Purchases- Requisitions will not enable you to display fields from the second level, Requisitions-Products (as you have discovered). You must create calculated fields in Requisitions which follow the pattern Requisitions Description=:: Products Description. Do this for each of the Products fields you want to display in Purchases and you can then display the new fields in your purchase orders.

[bob sherman]

Thanks. I tried the calculation but received the message that this would create a circular definition and would not allow me to proceed.

[Vaughan]

The calculation needs to reference the *related* field, not the field you have just created.

[bob sherman]

My primary relationship is between the Requisition File and the Product File so that when a product code is entered onto a requisition form, all product information is displayed. The calculation I created was:

(Requisition File ) Product = Product List::Product

The next relationship is between the requisition File and a Purchase Order File. I want my PO to display all information in a portal which is related in the Requisition File by a purchase order number pasted in the record of each request.

Thanks for your help

[dmartens]

I think I have this straight. The relationship between the Product db & the Requisition db is 1 to 1? 1 product to 1 requisition? If so, create calculation fields in the requisition db that reference the fields you want to show from the Product db to the Purchase Order db. Show those calc fields in your portal in the PO db.

If the relationship is Many to One (many Products to 1 requistion) it gets more complicated. In this case the PO db will have to reference the Product db & the REquisition db, in effect becoming a join file for the two related db's.

See this article in the FMP knowledge base: http://www.filemaker.com/ti/104456.html

[Vaughan]

If the relationship is strictly 1 to 1 then it doesn't need to be relational at all -- it'd be simpler being in the same record in the same database.

[djgogi]

And what if the entities(from 1 -1 relationship) are involved in other (possibly many to many) relationships with distinct objects (entities), or even what if domain of their attributes has not an empty intersection?

Dj