Search across 4 fields and report results

[shayneg]

Hi, Hopefully some of you clever people can help me out . . .

 

I have 4 fields called Engineer1, Engineer2, Engineer3, Engineer4 which have to be validated via a value list called 'Staff'. I have it in FM 5.5 where they click the field and a pop up list displays the values from the 'Staff' value list.

 

I then have a seperate field next to each engineer called Time1, Time2 etc. Each time they do some work on a customers job they add time to the Time field coresponding to each engineer

i.e:

Enginner1 Time1

Enginner2 Time2 etc

 

The time field is simply a strict numeric field

 

I need to write a script to search across the 4 engineer fields for say "bob moor" between 2 dates. This should then give me how many tickets that engineer has been on that week but as I don't know which engineer field he could be in I need to report across all 4 in the date range.

 

Sounds awkward but we have multiple technicians working on some jobs

 

Any help would be most appreciated.

[AHunter3]

Create a calculation field, result type "text" defined as Engineer1 & "¶" & Engineeer2 & "¶" & Engineer3 & "¶" & Engineer4.

 

Do your find in the calc field for whatever engineer you wish to look for ("bob moor") between dates. (Wherever the heck your date field is, you did not say. Hopefully you don't have four date fields as well?!)

[Techphan]

Create a calculation field, result type "text" defined as Engineer1 & "¶" & Engineeer2 & "¶" & Engineer3 & "¶" & Engineer4.

 

Do your find in the calc field for whatever engineer you wish to look for ("bob moor") between dates. (Wherever the heck your date field is, you did not say. Hopefully you don't have four date fields as well?!)

 

To Al:

 

If the OP were to migrate to FMP12 would you suggest that all of the engineer fields be merged into one field with a second field designating 1, 2, 3 or 4?

 

I was trying to think of the advantages and disadvantages of that versus maintaining the existing field structure.

[shayneg]

OK, the calculation field worked a treat and I can now search just on that new Calculation field

Now my problem is in my layout next to the engineers name there is a time field. when searching I need to work out the number of minutes next to that user.

i.e if I search 100 records across 4 fields using the new calculation I will find bob on say 20 records but how do I then get a total of bobs minutes across the 20 records and remembering that again the time for bob could be in either Time1, Time2, Time3 or Time4

 

Thanks in Advance

[AHunter3]

I would get rid of the entire Engineer1, Engineeer2, etc structure. Create a related file in which each record is the record of just one Engineer and that engineer's start and end time. Then relate them to the original record. I don't know what your original records are records OF but presumably they are jobs or tasks or some such thing.

 

 

"When I see Field1, Field2, .... Fieldn, I know there's something wrong with the design." — J. Wenmeekers