That's not how you would use the operator "and" as it is used to combine two values of either False or True (called Boolean values).
A Case function could be made to work for what you are attempting here, but every time you change what is in this list, you'll have to redefine your calculation.
I suggest that you use a very different method to produce the effect that you want where entering an ID looks up the description from a related table of such ID's and values.
See: Auto Fill
Sounds good. Makes since to use a table. I will check into that post.
So I created a table called "TDOT Item Numbers" and I have my original table called "Estimated Quantities".
The" Item No" field in the" Estimated Quantities" table has a value list populated with the Item Number field from the TDOT Item Numbers Table.
I then added the description field and unit field from the TDOT Item Numbers table to my Estimated Quantities Table.
I created a relationship using the item numbers because they are unique identifiers.
When I pick an item number from the drop down list in the estimated quantities table, FM auto completes the description and unit field with the correct values. All is good.
But, now I want to create a complete set of item numbers, descriptions and units for my project. And then I will fill out the quantities field for each day at the job site.
So I create Item NO. 2 and quantity 2 in the Estimated quantities table. I add Item no 2 to the relationship.
But FM doesn't know to auto populate the description field from the item no 2 chosen value. and now the first relationship doesn't work either.
So what should I do. I am sure I have again gone about this the wrong way.
Here is a second image.
You need third table and a portal:
Start with these relationships:
Estimated Quantities-----<LineItems>-----TDOT Item Numbers
Estimated Quantities::__pkEstimatedQuantID = LineItems::_fkEstimatedQuantID
TDOT Item Numbers::Item Number = LineItems::_fkItemNumberID
You can place a portal to LineItems on the Estimated Quantities layout to list and select TDOT Item Numbers records for each given Estimated Quantities record. Fields from TDOT Item Numbers can be included in the Portal to show additional info about each selected TDOT Item Numbers record and the _fkItemNumberID field can be set up with a value list for selecting TDOT Item Numbers records by their ID field.
For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained
So is the Line items table a duplicate of the TDOT item numbers Table?
It is a separate table, but some fields in it can be set up to look up data from TDOT if necessary. TDOT acts like a "product catalog" of items you might select for any number of different estimates. The LineITems table lists the specific items selected for a specific estimate.
This is much like an invoicing system with an invoice table with a record for each sale, a line items (or invoice data) table to list each item sold on that invoice and a products table that lists all products available for sale.
Ok Here is where I am at.
I created the Line Item Table and a portal into my Estimated Quantities Table.
So my TDOT table is my product catalog and my Line items table is used to build an invoice with selected items from my product catalog.
I still can't make a multiple line "invoice" with several different items from my "product catalog". Not sure where I am going wrong there. But I am assuming that I create each line of my invoice in the line items table and then portal the whole thing on to the estimated quantities table. Which means the only thing that would be in my estimated quantities table would be a date and a quantity box.
For my solution. Each Estimated Quantity record will be the same items but just different days and quantities.
Here is my relationship as it stands.
thanks so much for all the help.
Better take a closer look at what I posted the last time.
Your relationships are not correct. The match field used to link line items to estimated quantities cannot be the same match field to link it to TDOT. The match field in Estimated Quantities should be a field that uniquely identifies each record in Estimated quantities--typically an auto-entered serial number*. The match field in TDOT should also be a field that uniquely identifies each record in TDOT--best choice here is also an auto-entered serial number*.
And please note that you would put a portal to LineItems on the estimated quantities layout to use to list each item selected from TDOT.
* a text field with Get ( UUID ) as an auto-enter calculation could also be used