So the difficulty I have been facing is displaying ALL the inventory options on the Order Form. Because they are not related to the orders table I can't seem to have them all display...instead I start from the inventory page, and have created a script that creates a join table entry for each item that has been added to the order.
There has to be an easier way to do this because this solution creates several problems: updating an order become quite a hassle and the script isn't all that pretty either.
So I'm still wondering what would be the easiest way to allow people to scroll down the entire inventory list and just check off items they need, or fill in quantities that when committed, are subtracted from the inventory and added to the order overview.
Any and all help would be greatly appreciated.
To show all items in a relationship, use X instead of =.
You can define a link between your order and inventory tables that uses this cross product or "cartesian join" operator instead of the default =. To change the operator, you double click the line linking the two table occurrences in the relationship graph to bring up a dialog where you can specify the operator and many other options. Since X is an operator that tells filemaker to match all values, as far as I know, you can match up pretty much any pair of fields between the two table occurrences to make this work.
Depending on what other relationships you've defined, you may have to create a new table occurrence of your Inventory table to do this. To make a new table occurrence, you can select an existing table occurrence with the same data source table (such as Inventory in you database), and click the button with two green plus signs.
Once you accomplished the above task, put a portal on your layout that points to the appropriate table occurrence.
Many folks find the terms Table and Table Occurrence confusing. To learn more, click the following link:
Table vs. Table Occurrence (Tutorial)
Thanks so much for the advice, definitely a step in the right direction. I have the portal and it's displaying all the items on the orders page, now I'm just wondering that once I go through the portal and select all the items I'd like to place an order for, who can I have the portal constrain the inventory list? I could use a portal filter but what I'm really aiming for is an order overview page with a total amount that can be saved for future reference.
I guess I could use 2 separate layouts to achieve this...but maybe there's a cleaner solution.
What exactly do you mean by "constrain the inventory list"? Do you want items with inventory zero excluded from the list? Or do you want a list only of the items purchased on that invoice? The first option can be tricky to do because of stored/unstored issues. The second option is pretty easy.
I'd need the second option, a final list of all purchased items...preferably on that can be modified or updated if needs change. THanks so much for your help.
OK, what you need is an additional table and a 2nd portal to display the list of items purchased for that specific invoice.
The typical relationship for an invoicing system looks like this:
Which are usually defined as
Invoice::InvoiceID = LineItems::InvoiceID (Select delete and create options for LineItems in the relationship details)
LineItems::ItemID = Products::ItemID
We've added a third to list all the products:
Invoice::InvoiceID X AllProducts::ItemID (AllProducts is a 2nd table occurrence of products.)
You can place a button in the rows of the AllProducts portal (or make the entire row of fields a button) that runs this script:
Set Variable [ $ItemID ; Value: AllProducts::ItemID ]
Set variable [$InvoiceID ; Value: Invoices::InvoiceID ]
Go To Layout [LineItems (LineItems)]
Set field [ LineItems::InvoiceID ; $InvoiceID]
Set field [ LineItems::ItemID ; $ItemID]
Go To layout [Original Layout]
Now, selecting an item in your AllProducts portal adds it to your LineItems portal.
Note, there are numerous threads that discuss invoicing. You may want to click the advanced search link at the top of your screen and find a few of these threads to read up more on how to set up an invoicing database system.
Phil...thank you so very much for your help. It's really opened this whole thing up for me. It's all looking good. Thanks again, hope I can take it from here.