What do you mean by "conditional formatting"?
What exactly did you set up?
Conditional formatting changes the color, size, style of text or a field and that doesn't seem to be what you are describing here.
Sorry, conditional value lists.
I'll get my coat......
At first read, that sounds like a "hierarchical conditional value list" where you have a "chain" of conditional value lists Selecting a value in field 1 filters the available values in field 2. Selecting a value in field 2, filters the available values in Field 3....
But then you say:
the look-up will display the first price from the item selected in to the first layer of conditional
And I don't get what you mean by that as what you show should only match to a single record in your products table and only be able to return a single price.
If I have correctly described your "layered" value lists, you may find it helpful to compare your design to the examples found in:
"Adventures in FileMaking #1 - Conditional Value Lists". It has several different hierarchical conditional value list examples--each with detailed explanations of how they work and how to set them up.
Thanks again Phil. Looking at this I think maybe I need a "Hierarchical Conditional Value List", rather than a "Basic Conditional Value List". I'll make some changes and test.
I'm still struggling with this one. I'm told I should use a bit of SQL script to pull through the relevant price. However its not working.....
ExecuteSQL("SELECT price FROM MaterialItems WHERE Brand = ? AND GSM = ? AND PSize = ? AND Packed =?";"";"";MaterialLines::Brand;MaterialLines::GSM;MaterialLines::PSize;MaterialLines::Packed)
Any obvious mistakes?
OK so this seems to have fixed it.....
In FileMaker SQL queries, it is often necessary to enclose table occurrence and field names in double quotes inside the quoted string that makes up the first parameter of the ExecuteSQL function. One way to insert that double quote character into a quoted string is to precede it with the backslash character. So in the above queries, \"_fkCountyID\" will evaluate as "_fkCountyID".
Some useful SQL Resources:
SeedCode's SQL Explorer: http://www.seedcode.com/cp-app/ste_cat/sqlxfree
FileMaker inc's reference Doc on SQL: https://fmhelp.filemaker.com/docs/13/en/fm13_sql_reference.pdf
And a custom function, (FileMaker Advanced Only), that can result in FileMaker returning actual error text instead of the infamous ? result when you have a syntax error:
//the sql call results in an error, return empty so the error will be returned
_executeSQL = "?" ; "" ;
//the sql call is executed correctly, just return the result
This function is published on FileMaker Custom Functions
to check for updates and provide feedback and bug reports
please visit http://www.fmfunctions.com/fid/335
Prototype: sql.debug( _executeSQL )
Function Author: Andries Heylen (http://www.fmfunctions.com/mid/57)
Last updated: 28 July 2012
To use, test your query as a "watch" expression in the data viewer, enclosed inside a call to SQL.debug. Click Monitor and then click the edit button re-open the specify calculation dialog. The Evaluate field at the bottom of the dialog will now display an error message instead of ?.
Thanks very much Phil.
I have a new problem now though ;)
Don't think this is a new problem, probably not spotted it before getting to this point. So I have 4 levels of Conditional Value Lists, which should be narrowing down your options, however they seem to sometimes give you options that aren't available. If you see what I mean. Not sure how to explain this, I'll try some screen grabs.....
this option is presented by the drop downs....
but by doing a search in the materials item tables, this item doesn't exist
relationship between tables
Settings for Value List
I was under the impression that the 4 conditional value lists would narrow down the options at each stage. They seem to. But obviously aren't going quite far enough.
You have 4 value lists in your screen shot, but you have only shown the set up for one of them. And if this is intended to be hierarchical where each value list controls the available values for the next, you'd need more relationships and the one shown would not be the relationship used for the value list setup shown as you'd be using a relationship (or SQL query) that matches values by Brand, GSM and Size.
My best guess is that one or more of the values in the fields leading up to "Packed" should have prevented "Bulk Packed" from being an available option but doesn't.
Thats exactly the problem Phil, thanks for explaining better than I could.
How should I set up the relationships so it works correctly?