I need to fetch the most recent product cost from the date of the purchase order which will be auto-entered in PO_LINES table.
Product Cost is maintained in a separate table.
OK. I misunderstood your initial description. You could still create a PO_Date field in the PO_LINES table and have it populate as new lines are added. Then you could establish the relationship I described above and it would work. I realize this is denormalization of the database, but sometimes it is necessary to make things work.
If it were me, I would probably make this a scripted process so that as new lines are added in the PO_Lines table, the script fires and fetches the most recent PRODUCT_COST (perhaps an OnObjectValidate script trigger on the PO_LINES product_id field). You could use ExecuteSQL to accomplish it (if you know eSQL, it would be really pretty simple to do...that's what I would use).
You could also do something like this:
Establish a global field in the PO_LINES table to house the PO date field.
Create the relationship I described above, but this time use the global date field to connect to the PRODUCT_COST.RECENT TOGFor your script, set PO Date field into the global field stated above (that makes the relationship work so that the most recent product cost is accessible.
When you write, "fetch..." how are you going to use this data? I ask because there are several ways to go about fetching data. Establishing a relationship that will constantly evaluate may or may not be what you are after.
However, if I understand your scenario correctly, you will be setting the PO date in the PO_Lines table, correct? If so, to establish a relationship you can add a new TOG perhaps called PRODUCT_COST.RECENT (based on the product_cost table) and drag the _kf_product_id from PO_Lines to the _kf_product_ID field in this new TOG. With the relationship dialog open, connect the PO_Lines date field to the PRODUCT_COST.RECENT date_purchased field and indicate the operator you desire. Because FileMaker's native sort is on record creation (first created is first shown), you will likely want to sort the relationship to sort descending on an appropriate field (I usually utilize serial numbers in my tables for this purpose). Be warned, however, that sorting the relationship can affect performance as record number grow...which is one of the reasons I asked for the purpose earlier.
Hope that helps and good luck!
The cost will be auto entered in the cost field of po_lines table. That will be multiplied by quantity to get total cost.
po_lines won’t have date field since purchase order table will determined the date.
I your structure, I'd add two fields to the PO Lines table:
You can fill in the projected unit cost when you fill out the PO. That cost is either the last cost of that item or the most recent data on the cost sheet from the supplier. The actual unit cost would be filled in when you receive the item.
Having said that, I strongly advise having a separate Receipt and Receipt Line table. Consider a scenario where you order 100 widgets at 0.10 per unit. On Monday, you receive 90 of those units and the actual cost was indeed 0.10. On Thursday, you receive the remaining 10 units from that order, but the actual cost is now 0.11 per unit. Your current structure has no way to track that.
I wrote an article about this which may be helpful: Receiving Goods
Thank you, hillio-dillio. Your detailed explanation is very helpful.
Looks like there's no way to link the date without creating that again in PO_Lines.
Thank you, Daniel. Your articles are very helpful.
Retrieving data ...