Seems like you have two tables where you need three:
Orders::__pkOrderID = LineItems::_fkOrderID
Products::__pkProductID = LIneItems::_fkProductID
And you'd use a portal to LineItems, not products for listing the produces for a given order.
You can examine the Invoices starter solutions that come with FileMaker 11 and 12 to see working examples of this set of relationships though the table and field names will differ from what I have used here.
For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained
Thanks, I thought the problem sounded familiar.
On the Order layout I've got the portal for 'Line items' partially working: The pop-up value list for products functions and I can add/delete these items. However, there's a Product unit field I'd like shown in the portal. I've tried making the 'Line item: Unit' = Product::Unit, but the portal field remains emptyi.
If this is just to display data from that field in the product field, you can just add that field from Products to your portal row. If you need to copy that data into Line Items so that future changes to the field in Products do not change the value in existing line items (Pricing works this way), then a field in line items can be set to use either an auto-enter calculation or a looked up value setting to copy the value over from the corresponding field in products.
In neither case should you include this field as part of your relationship.
I've tried putting both fields into the portal:
- Product::Unit field and
- Line item:Unit where this is calculated as Product::Unit
without getting anything in the Unit field in the portal.
BTW relationships permit creation/deletion of records in Line items for both:
Order < Line items and Line items > Products.
"Allow creation..." has nothing to do with this and I would not enable it for either table in the line items to products relationship.
Either your line item record correctly matches to a record in product or it does not. If it does not match to a record in product, any field from Product that you put in the line item portal row will be empty. If it matches, that field will show data from the related products record.
The problem was that my _ProductID (fk) wasn't numbering. I've put in a calculation of _ProductID=Product (ie. the one chosen from the Product value list). Seems to work. Is it best to keep fk's like _ProductID as 'prohibit modification during user entry'?
LineItems::ProductID should be the field that you format as the value list for selecting a product in your portal row and this should also be the match field that links to LineItems to Product.
Odd that my workaround actually worked; you're correct. Users need to check that Layout > Data > Data formatting > set to general, otherwise text won't display.
Users need to check that Layout > Data > Data formatting > set to general, otherwise text won't display.
That should not be the case. Is there any chance that you are trying to put text in a field of type number?
So you mean I should have formatted my _ProductsID(fk) in the Listitems table as text to make it compatible with values from the 2nd field shown via the value list?
No. I thought you were referring to the units field from products.
the data format specified for the ID field--which should be of type number, has no effect on what data will appear in other fields on the layout as this does not change the value of the field.