Value list in another file delineated by ....

[merkaba22]

Still learning and I have another question:) Is this possible or is there an easier way?

I have an Inventory file with a record for each item offered by a manufacturer.

I have a script that generates a PO record in a PO file comprising a random set of items by one of the manufacturers (in 16 possible fields: Product ID 1-16 in the PO file) and a creation date. For example, in one PO record any one item could be ProductID1 and in another record it could be ProductID8....

How can I create a value list (in any given record in Inventory) of the creation dates in the PO file where that given record occurs so that all the data associated by a particular ProductID1-16 can be accessed by lookup?

In other words, the intent would be to have pop-up list (via a value list related to the PO file) in a record in Inventory which displays only the dates of the records the PO file where the item was ordered so that in chosing a particular date form the list the associated data for that item in PO would be displayed by lookup ....

[transpower]

A better database architecture would be item, purchase_order, purchase_order_line_item (a join file). The portal in purchase_order would show purchase_order_line_items related to the purchase order. The portal in item would show purchase_orders related to item.

[merkaba22]

I am not sure if I got this right yet?

Since there is no fixed field for item in PO I am not sure how to create that join; ie. I could have 0101004_productID1 and 010204_productID2 for the same item ordered in seperate POs on seperate dates; how can I access that through a portal?

Thanks:)

[transpower]

Allow creation of new purchase_order_line_item in the portal in the purchase_order (you could use a pop-up list to choose from the items). Do not allow creation of new purchase orders in the portal in item.

[merkaba22]

I am grateful for your suggestion and I have been working on this since your last repsonse.

I am a newbie at join fields and I still am a bit confused in the general layout. Here's what I did:

In a new (join file) called PO_PI.fp5 I created two relationships:

1) POID from PO_ID to PurchaseOrders_ID in POs

2) PIID from PI_ID to ProductInventory_ID in Product Inventory

two calc fields:

1) POID::Date (in PO)

2) PIID::MfgrItemNum (in PI)

dfn: relationship to join two files:

PIPO1 from ProductInventoryID to PI_ID

Created a portal in PI & placed related field (PO_IN) in th poral.

dfn: realtionship to join two files:

POID2 from PurchaseOrder_ID to PO_ID (in POs)

I didn't see anything in the portal at all ... and I don;t think I am approachinng this correctly yet.

The user wants to see all the dates of the POs he's created for a given item (record) in PI where in each record of the PO the item can be found in any one of a range of un-related fields called ProductID1 through 16 (16 possible fields); so that in chosing the item in the portal, all the related data to that particular ProductIDx in that particular PO could be displayed in PI in a layout.

If I understand your concept, there would be a portal in PI to show all the dates in PO where the item was associatd in the PO through ProductIDx. I am not sure how to construct this. As a beginner here it would seem that the portal would have to listed a new field name (creation date+ProductID 1 through 16) where the user could choose the item in the portal but I am not sure how to go about that either .....

[transpower]

It's difficult for me to understand your post. If you were in version 7, you could post your Relationship Graph, so we could check whether or not the primary and foreign keys are properly related. For notational clarity, you could use _fk as the suffix for a foreign key and _pk as a suffix for the primary key. The join file will have two foreign keys. The purchase order file will have a primary key (the order number), and the inventory item file will have a primary key (the item number). Once you have the primary and foreign keys linked, you will see the records in the portals. So: go back and check your portal definitions.

[merkaba22]

I had to approach it from another way:

I have a portal on the item layout in PI which returns all dates of PO records where the maufacturer of the item is sent a PO -- filtering the dates by the manufacturer of the item.

I then expanded the portal to show the date and all the ProductIDs 1-20 in PO, a field in the portal row. I explored this because the items in PO records could and do show up anywhere in one of 20 ProductID fields: ProductID1, ProductID2, and so on .... but only once in each record -- ie. they are not related, per se.

So I have a portal w/ fields for the date, Product ID 1, Product ID 2, etc. -- each ProductIDx has a button that triggers a script related to the date in the portal:

SetField ["POitem", "POinfo::ProductID 1"]

SetField ["POdate", "POinfo::Date"]

SetField ["PO Ringsize2", "POinfo::RingSizeID 1"]

SetField ["PO Stonesize2", "POinfo::StoneSizeID 1"]

etc.

and it works!!!

But the ideal solution would provide a way for the user to simply choose from only a date from the portal for the record, the ProductID's being determined by the record of the item and in doing so all the set fields would be run without the user having to view the 20 (of ProductID1-20) fields for the record item of choice. That would be nice:)

Can you do that in FM6?

I am not sure if the way you gave me originally would do this too -- I had too many questions....

Is this preferable, clearer or am I making this harder than it has to be?

Thanks once again, you are great:)

POlayout.pdf