I need a design solution for a database that creates a weekly order summary for a small business. The ordering is done on one day per week, and there are about 50 items. I want people to be able to select the items that need to be purchased on an iPad, so they will not have a scroll/mouse function. So I need to display the items for purchase in a conditional value list. I am having a conceptual problem with how these lists work ; how table occurrences have a contextual 'view' of the data, and asking my database to look for the right values, from the right viewpoints.
Ideally, I'd like to have items for the weeks purchase order entered through a Type - Item CVL, so minimize input hassles. I'd like to be able to generate an email to each of the three suppliers, containing the items/serial numbers/order quantity and purchase order date automatically.
My first task is to create the CVL's. I am really kind of lost on how to design this properly;
I need to create a layout with the date, and a 2 levels of pop-up boxes (Type/Item) that enters an item into the orders table with todays date - the date and item particulars forming a single record - and then creating my purchase order by sorting all the orders by date, and then by supplier, to create the three purchase order emails
I don't know if this is best done by using a portal, or not, I have seen a tutorial on CVL's that uses a "Line Items" table, and an Invoice table.
Here is what I have in terms of tables and relationships. I have tried the Conditional value lists as follows;
My layout is "orders" based on the "Orders" table. iIt looks like this
Orders::CATEGORY is a pop up box, based on a value list "Category VL" CategoryVL is based on a field, ITEMS::Category (Use all values).
Orders::ITEMS_FK is a popup box based on a value list "ITEMS VL" . ITEMS VL is based on a field, ITMES::ITEM_FK (include only related values starting from Items2)
Any ideas for making the CVLs work? I think it is in the relationships, and specifically, the table occurrences and the value list definitions as to where the Filemaker 'looks' from. But i Cant see it. Also, if someone has a more elegant method of design for my function, so far I'm not too far with this.... Any help appreciated.