I found this:
It sounds like you are actually dealing with multiple issues and I suggest that you break this down and tackle them one at a time.
- On the one hand, you need to be able to compute a cost based on multiple field inputs plus looked up data based on the type of material specified. That is a potentially very complex issue with multiple possible methods available for solving it.
- But you also mention the fact that the lists of possible values in one field will be controlled by a value entered or selected in another. That's something we call a conditional value list and is a much more straight forward piece of the entire puzzle.
- And there could be much more to that that I've missed just from that "first read" of your initial post.
Let us know which or both you are dealing with and then please provide as detailed an example as possible of the first issue. Show us some of the inputs you might need to specify to calculate the cost and try to give us some idea as to the potential variation that may occur from one material to the next in producing your quote.
Also let us know the structure of your tables or relationships so that we can see if that structure will work with what we might choose to suggest and whether or not a change in that structure may be needed in order to produce the results that you want.
With regards to 1. above, I can outline three general approaches to handling that issue, but in each, the devil is in the details and thus I need to know a lot more before I can be specific about any of these:
1) Multiple Match fields populated from drop downs match to a single record in a look up table where a unit cost can be looked up (copied) to your record. Then a calculation takes a quantity and multiplies it by this unit price to compute the item price. Example, you specify a piece of Lumber as "Doug FIr", 2" x 4" x 6' and the system returns a cost in board feet or linear feet. Your calculation then uses that value and the total number of 6ft douglas fir 2x4's to compute a cost.
2) A unit price is looked up based on the type of material and then a formula is used that combines the different inputs and the quantity to compute the cost. While not realistic for a lumber yard, our 2x4 example used with this method is one where specifying Doug Fir looks up the cost of one cubic inch of material and then the specified L x W x H compute the cost.
3) In some complex cases, what you look up from the look up table is not just a unit cost but the very formula required to compute that cost could also be looked up from the table. Thus, you might look up a formula for the area of a circle for one material, the volumne of a cylinder for another while others might be based on cubic volume or linear feet.
And such look up tables do not need to match values exactly in all cases. Look up tables and the relationships that link to them can be set up such that values that fall into specific ranges of values all match to the same unit price.
I have a whole list of instructional links that I routinely share for those that need to learn more about conditional value lists so feel free to ask for those if you need them.
Great advice, breaking it down. I have decided to simplify what I want the system to do, and I think that I can get it to work within my skill set. Then, when the boss is convinced, I can hire someone to take it to the next level. Filemaker is so amazing, I want to really use it to it's full potential you know?
So question #1.
From a "Quote Main" layout, I click on New record and create a new quote with a unique Quote ID. (Get UUID) This number will tie the quote to each line item in the relational database.
To add a line item, I have a button tied to a script that copies the QuoteID from the main layout, then opens the "Line Item" layout and pastes the QuoteID there, commits the record, and returns to the "Quote Main" layout. The line item is then added through a portal.
This works fine, but requires that I have the ID field displayed on each layout. Not terrible, but clunky.
is there a better way to accomplish copying an ID field from one table to related table?
And a "conditional value list" seems to be what I am looking for. Any more info would be appreciated. That should get me to the next step.
Not terrible, but clunky.
The copy script steps should be avoided if at all possible and one is not needed here. Use Set Variable in place of copy to set a variable to the value of this field and set field in place of paste to copy the value in the variable into a field in the new line item record. Now you no longer need the field to be visible (and worse, editable) on each layout. Also, your users will be happier now that data they may have copied to the clipboard is not being mysteriously replaced by data copied to the clipboard by your script.
And you can do this without any script at all in many cases. If you put a portal to LineItems on your Quotes layout and enable the "allow creation of records via this relationship" option for LineItems in the relationship, you can create new records in the portal without needing any script at all.
And here are my links on Conditional Value Lists:
There's a lot of overlap between the first two links so you can read one and skim the other. The demo file gives you several examples of different conditional value lists so you can examine them to see how they are set up. If you are using FileMaker 12, you can open this demo file from your File menu to get a converted copy of the demo file that you can examine in FileMaker 12.
The last link discusses how to set up a chain of conditional value lists where the value selected in each conditional value list controls the values listed in the next value list.
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.
I'll read through all of the links (thanks a million!) but I am not 100% clear on the set variable. When I create a new record, an ID is created automatically with Get(UUID). That same number has to copy over to the related table so that they match. Set Variable seems to be a way to create a number and paste it somewhere, but it won't match anything else.
The standard script for creating a related record (lineItem) from the context of the parent record (Parent):
The script looks like this, but substitute your names for the place holders in the example script:
Set Variable [$ID ; value: LayoutTableOccurrence::PrimaryKey ] ---> instead of copy
Go to Layout [ "PortalTableOccurrence" (PortalTableOccurrence) ]
Set Field [PortalTableOccurrence::ForeighKey ; $ID ] ----> instead of paste
Go To Layout [original layout]
This script assumes that your relationship for the portal is: LayoutTableOccurrence::PrimaryKey = PortalTableOccurrence::ForeignKey.
Be careful about script triggers specified on either of the two layouts as the change in layouts can trip a number of script triggers and the script they perform pops up in the middle of the above script and this can interfere with your results.
Thanks for the feedback. I am going to play with this and see what it does. If I understand this correctly however, the script will create a number (of my choosing) in the PrimaryKey field, then create the same number in the ForiegnKey field.
It seems to me that will not work for adding multiple line items. Each time the script is run, the Primary Field will be overwritten and only the last line item from the ForiegnKey table will link back. I need one number in the table with the PrimaryKey to match multiple lines in the table with eht ForiegnKey.
And what happens when I make a new record for the PrimaryKey table? Won't the next record have the same ID as the last?
The script does not change the primary field's value at all. It should be an auto-entered serial number or Get (UUID) and not something that the user can ever edit. You can run this script as many times as you need
And remember that with a portal, you can do this in the portal without using any script.
I'm afraid that I just don't understand what this script is trying to do, nor can I get it to work. Too inexperienced with scripting at this point.
However, getting rid of the script seems to have done the trick. Without a button (Add Line Item) and a corresponding script, the portal is now working how I want. If I click on the first field in the portal, and then tab to the next, the ID value from the Parent Table is copied automatically to the matching field in the child table. So it seems that Filemaker does it automatically, wish I knew that a week ago!
I was struggling with the button because I saw it on another demo, and it also took the user to the next available record. Perhaps I should just concentrate on that.
And a big thanks to PhilModJunk for your patience! (I realize now that I have mastered riding a bicycle, and am asking how to fly the Space Shuttle.) I am just not at a level to understand anything beyond the most basic functions of FileMaker. I will go and get some formal training and master the basics before I try to complete this project!