You can modify that approach by including a factory ID field in two of the tables. This can be used in a relationship to products to get a conditional value list of only the products manufactured by that factory. From your example, it looks like the same model is not produced by two or more factories. Is this correct? (Multiple factories producing the same model can be handled, but it's an important detail to keep in mind.)
Will you need to specify the factory once for each order or might you specify different factories in the same order?
The difference is whether you link your orders table or the line items table to the products table using the new FactoryID field.
Links on conditional value lists:
Forum Tutorial: Custom Value List?
Knowledgebase article: http://help.filemaker.com/app/answers/detail/a_id/5833/kw/conditional%20value%20list
Hierarchical Conditional Value lists: Conditional Value List Question
Feel free to post follow up questions here if those links aren't enough to get your value lists working correctly.
You are right, one model is produced by only one facotry.
I am working on specify more than one facotry in order too! What would be your advice to that?
Thank you so much!
One facotry may have dozens models attached, I was thinking after select one factory, I can see all model linked to this factory. In order table, it may look like this in layout:
Factory Model Quantity
Then I will enter numbers in quantity field. It works like a portal after I select the factory, while portal records will be replaced after I select the same factory again and enter new Qty number.
Conditional value list works great to select model, while If I need to place order with more than hundred models in one factory, I may have to select many times to get all models I need.
Is it possible to show all model records under the same factory in layout?
Many thanks Phil!!
Value lists are the quickest and easiest method for a new developer to set up. As the list of potential values becomes large, they are not as user friendly as other methods that can be used. The value list and conditional value lists still represent a good starting point.
Are you able to set up a conditional value list that works for you (except for the fact that the list is so long...)?
If so, you can set up a portal to products that uses the same relationship. Then clicking a row in this portal can perform a script that enters the ProductID of the selected product into a new record in your line items table. This can be set up to look and function like a checkbox list:
Select Factory from value list and see list of models in portal. Click a row and it's added to your line items table.
Your script might look like this:
Set variable [$InvoiceID ; value: Invoices::InvoiceID]
Set variable[$ProductID ; value: ProductsByFactory::ProductID ]
Go to layout [LineItems (LineItems)]
Set field [LineItems::InvoiceID ; $InvoiceID ]
Set Field [LineItems::ProductID ; $ProductID ]
Go to Layout [original layout]
TO enter the quantity, you can add a show custom dialog step that uses an input field to get the quantity or you can enter that in the line items portal after clicking to select a portal from the list of models from a specified factory. If you use this second option, you can set up a sort order on your portal so that the most recently ordered item sorts to the top of the items in the portal for easy editing.
Thank you Phil! I can set up conditional value list, thanks for the script, I will try on this.
On more question, how to show all records in one database on it's layout?
In one Table? (a database can consist of many files and many tables..)
Show All Records will show all the records in the table--this would be for list or table view.
If you want to show all records in a portal, you'll need to describe what you have in mind in more detail.
Yes in one table.
For example, I have a shipping table, in which i have fields: Factory, Model, Container, ETD,ETA. They all exist in one table.
I was thinking to see all shipping records I stored in this table on layout, looks like this:
Factory Model Container ETD ETA
a a1 1122 June July
a a2 1122 June July
b b1 3344 July Aug
And I have portals built in in this layout, I was thinking if I click on the first record then the portal information will change accordingly.
A list or table view can be set up with Show All records to list what you show there.
The portals might be placed in the header or footer of this layout. (I recommend list view instead of table view for this.)
I tried with List(Factory) in same table, while it only catches the according value in one record not all.
How can I get list of all factories?
I didn't say anything about using the list function. I am talking about using the list VIEW--an option you can select in the View menu or specify in a script step.
List is an aggregate function just like, sum, count, average, etc.
aggregate functions use one of the following syntax:
List ( repeatingField ) -- produces return separated list of all repetitions that contain data
list ( field1 ; field2 ; field3 ) -- produces a list of these fields, omitting any that are empty
List (RelatedTable::Field ) -- produces a return separated list of values from all of the related records where Field is not empty.
Sorry I misunderstood...
Thank you Phil!
I was thinking is it possible to write a loop and read every row in portal, then save the records to fields?
Yes, but why do you need to "save to fields" in this case?
And why do you need such a loop?
Because now I have a Order table with portal which I can draw all models related with the specified facotry. After I type in quantities for each model, the portal with background calculation will show me information like Total amount. So I want to save information from the portal to Order table.