Not sure I picture all that you have in mind here. An example with some actual data might help.
My first choice here would be to consider a related table rather than a repeating field or separate fields--9 times out of 10 that's really the simpler approach in the long run.
An example would be where we need to measure 4 different things on a sample, say voltage, current, resistance and power. I added a screenshot to show the form.
The form has space to enter the criterea and eupiment used for each of the tests, so I have fields like testEQ1-4, min1-4, max1-4, etc
It is possible that all four tests could be performed on a different meter so testEQ would be different for each instance or all could be done with the same meter or a combination. Regardless I would like each of the testEQ fields to be a drop down value list based on past entered values from all 4. I though using a single testEQ field with 4 repetitions would be easiest, but I found an old post that mentioned using a calculated field - List (testEQ1;TestEQ2; TestEQ3; TestEQ4) would give all of the values entered into anyone of the four without having duplicates. Before I found that I was thinking of using repeating fields.
The other issue where I want the value entered into Data::test1 to be conditionally formated to Form::min and Form::max ; Data::test2 to Form::min2 and Form::max2; etc - make the text red if it is outside those values. This had me leaning toward separate fields.
I don't understand the relationship between the fields in the top of your screen and the portal at the bottom. How do you use that portal and what data should it display?
And this is one record for 4 sets of fields and a portal? (I'd tyr to use 4 records with 1 set of fields and maybe place the portal in a footer here...)
Yes this is one record in the ETform table, the fields in the upper section are in that table. The bottom section is a portal to the Data table. There is also a section above what is shown in the screenshot that has fields from the Main table showing the job number, customer name, part type, etc.
The fields in the top portions describe the test being performed, the equipment used and specifications for that test. Each one of those four correspond to one of the columns in the lower portion. The bottom portion is a portal to the Data table with fields for Serial number, test1, test2, test3 and test4.
So the first block in the upper section, section 1, would define what Test1 is and the second column in the portal is the result of that test, to be conditionally formated so that it is red if not between the values in the Min and Max fields of section 1 above. Section 2 is for the third column and so on. The first column in the portal is the serial number of the sample being tested. The portal has up to 12 rows, accomadating up to 12 samples. If there are fewer than 12, there are blank rows, if more than 12 another page is generated.
The relationships for the tables is like this
Main table -(MastertrackID)--<many formTables>-(MasterTrackID and TestID)--Data
I'm sorry but I just don't follow the logic of your current design and I can't suggest alternatives until I do.
How does the data entered into sections 1 - 4 in the ETform table end up in the records that make up the portal's records from the Data table?
Your portal shows 5 columns only one of which is labeled (colum 1 = serial number ). If columns 2 and 3 are for section 1, columns 4 and 5 are for section 2, where do you record the data for sections 3 and 4?
I'm reading section 1 as the first set of fields shown above, section 2 the second set, etc.
Ok I didn't explain that well. Say the first test you do on the part is measure the resistance, in section 1 you would fill in
Test performed = Resistance, Test Equipment = Fluke ohmmeter, calibration date = 11/09/2011, Min = 100 ohms, max = 200 ohms. There is typically more than one test performed so there are up to 4 sections.
Then in the bottom section, the top line even with "Serial Number" you can label each of the columns, so you would label the second column "Resistance". The third column corresponds to Sect 2, the fourth to Sect 3 and last to Sect 4. (actually I think I will just make those column headers other occurrences of the TestPerformed field.)
Then you make the measurement on the first part, enter its S/N under serial number and what ever measured value under Resistance (the 2nd column)
Then the measure the second part entering its S/N and the measured value in the next portal row, and so on until you are out of samples or fill in all 12 rows. If you still have samples after 12 there is a button to create a new page, which duplicates the record except for the TestID field(autoenter serial number), so the form is the same except the portal rows are blank since the TestID is different.
Once you are done with that job, you grab the next job and there is a new job button which askes for the new job number and creates a new blank form.
This is kind of set up to fit the way we do the jobs, typically a technician sits down to do a specific test and has a bunch of jobs. So he would grab the first job from the stack - enter the tests to be performed and the limits (fill out the top sections)- do the tests, entering the data as he goes (filling out the lower portion) - then grabbing the next job from the stack - repeat.....
Ok, so in that case, if you use the four tests in your example, the conditional value list would be a list of those four test names controlled by what data is entered into the 4 test performed fields? And only the values entered into this record?
That we can do fairly easiy with some hidden calculation fields and a self join relationship:
cTestNameList could be defined as List ( Testperformed1 ; TestPerformed2 ; TestPerformed3 ; TestPerformed4 )
And a relationship:
Table::TableID = SelfTable::TableID
Would allow you to list values from Table::cTestNameList, include only related values, starting from Table.
But I'd be inclined to make those 4 sets of fields, 4 related records. Then the conditional value list can just refer to the Test Performed field in the portal's table.
And a relationship can be created between a field in each Data portal row that identifies the matching test performed record and the ID field of your main table to link it to its set of Data records can enable your conditional format expressions to match to the data from the correct related record.
Actually I was thinking of the value list for test equipment and that the list would have values from those four fields in all records. That way as each piece of equipment is used it will only have to be entered once and thereafter can be selected from the list. If I make it autocomplete as well the technician can just type the first few letters until it narrows down, or keep typing if it isnt there yet.
I found a post you made earlier where you created a calculation field, say for me it would be cEquip = List (Equip1; Equip2 ; Equip3 ;Equip4). Then set the value list to be based on the cEquip field. That seems to work for the value list so far.
Just keep in mind that if you made those top 4 sets of fields, related records in a portal, you would have one field, Equip, and you can just refer to the Equip field in this table with no extra calculation field needed.
I can see how that would make that part easier. I'm just starting to have trouble keeping track of all the tables and relationships I have so far . And given a little different application I can see other advantages as well.
Am fighting the conditional format formula now. Trying to get Data::test1 to be red if it is <min1 or >max1 Am sure I'm missing something simple in the formula.
"I'm just starting to have trouble keeping track of all the tables and relationships I have so far"
If you are unfamiliar with "Anchor Buoy", you may want to read this article on a way to organize table occurrences and relationships into a more coherent pattern that reflects the layouts you've chosen to create in your database: http://sixfriedrice.com/wp/six-fried-rice-methodology-part-2-anchor-buoy-and-data-structures/
With regards to the conditional formatting, do you want that answer in terms of your current design or from the perspective of using a portal for the top 4 groups of fields?
PS. using this portal I'm recommending also makes certain types of reports and searches much easier to create/perform--though such may or may not be needed for your particular system.
The only other place this data is used is a simliar layout that goes into the formal report. It looks very similar but has our company info, logo and a few other things to "pretty" it up.
I take it from the example you linked that Dan is just using Multiple TOs with different names to set things up in logical trees, in a ANCHOR_tablename convention. Mine so far is looking like a big fan where there is a link from the Root table back to a foreign table. Then the Root table to each of the form tables. Then each of the form tables to one or more TOs of the data table.
I guess I will try to go with the current set up. If I do add another table for the EQIP, I imagine I would just have to change the expresion to refer to the min and max fields of the correct portal row rather than (min1 and max1), (min2 and max2).........
Ever need to see all records where a specific piece of equipment was used to make a specific measurement?
Do you see any utility to a report that groups the equipment with the data it was used to collect?
A key part of Anchor buoy is that you start a new set of TO's for each main layout in your system with a TO used in "Show records from" for that layout and you only link in TO's to it that are needed for operations performed from that layout. This rule lays out an orderly structure that greatly reduces the "spider webbing" that can occur in FileMaker.
( Self > YourTable::Max1 ) or ( Self < YourTable::Min2 ) is an expression for column 1 that would allow you to specify a fill and/or text color for out of range values. If you were to separate these into two expressions:
Self > YourTable::max1
Self < YourTable::min1
then you can specify different colors for each type of range violation.
With the added portal I've suggested, you could use GetNthRecord in the expressions to specify the portal row.
BTW, is this a set of measurements where speed is important? That's implied by your use of different columns for each set of fields. There's a different approach possible where you use a one column for recording the data and either a second column for specifying which linked equipment record to use or a script triggered "synch" between the records shown in the data portal are controlled by the active portal row in the "equipment section" of your layout.
This can produce a format for your recorded data that can be much easier to work with for statistical analysis and such, but results in a layout that isn't quite as fast for recording the data.
In our application the data is only grouped with the one particular job. We don't ever compare data from one job to another job even if it was on the same piece of test equipment.
Typically the technician will fill in the "Test Performed" section(s) from the customer's requirements or data sheets.
Then take the first part and make a measurement -write it in the appropriate block - make the next measurement -write it in - and so on. Sometimes they will set up the first test and do each part, then the second test and do each part - where they are going down the columns rather than across the rows. It depends on whether it takes longer to change the test set-up or change the sample.
OK I tried several variations of (Self>ET::Max) or (Self<ET::Min) but it makes the text red regardless of the values entered. Then I realized that while I was smart enough to do that part right, I was dumb enough to have the Min and Max fields defined as text not numbers. Changed the field defs to number and it all works