different Lookup field based on a condition
I have this problem I've been trying to figure out for some time now but I can't seem to find a solution.
I'm working on my first Filemaker Project so I don't have a load of Filemaker experience, but I have made some Access databases before.
The problem I have is the following:
I want to make an invoice and in it is a portal with what we'll call "line items".
I have a table of products and each product has a price.
Now the trick is that each product actually has two prices. Depending on what type of job it is.
There is thus "Price1" and "Price2" in the Products table.
In my invoice I made a value list with two options. Let's say "Option 1" and "Option 2".
The idea would be that I first select one of the two options and that according to this option I get either "Price 1" or "Price 2".
I used a simple "IF" statement in the lineitem Price field:
This If statement is a calculated value in a number field.
If (tblInvoice::Option = "Option 1"; TblProduct::Price1 ; TblProduct::Price2)
Now what I found out was that it sometimes worked and sometimes it didn't.
Especially when I put in "Option 1" as Auto Value Data it did not seem to work.
What I found was that I needed to specifically commit the value list by hitting enter after choosing one of the options in the dropbox. Only then did my calculation work.
If I change the value in the dropbox I only get "Price 2" after hitting enter on the dropbox.
As if just choosing a value hasn't really committed the value in the field.
I also tried to make it a set of radio buttons but also those need to be committed.
The last thing I tried was to make my price field a 'calculation' field instead of a number field with calculated value.
The good thing is that it updates the values immediatly when I choose a different value. So even after imputting a whole list of items I can still change the price.
But the problem of the value list needing a hit of the enter key remains. Especially the Auto value data does not seem to get accepted.
Also what's missing is now that it isn't a lookup anymore. So when I change the price, my prices and totals in my old invoices also change. Which is not the idea.
I have not yet gotten into scripting but I was hoping there had to be some easy way to make this work.
Anyone have any ideas?
P.S. I am really loving Filemaker Pro.
But this seems to be the first big snag I've run into and I can't seem to find a solution.