Return Field Name with Quick Find?

[William Slim]

Hi all,

This is my first post here so Hello!

At work we are still stuck on Filemaker 11 systems wise but also have Filemaker 12 on hand.  Unfortunately my solutions must be built in Filemaker 11 so please forgive my ignorance if there is an obvious solution in newer versions (I've yet to read about any).

Does anyone know of a way to return the Field name where a positive was found via a Quick Find?  FM11 has the the ability to search all fields: Quick Find, return the fieldname: Get(FieldName) but I can't find a way of getting them to work together.

Many different clients send data to us to be loaded into our own database system.  The data we get from clients has to contain certain agreed fields of information but they also send us lots additional fields and ask us to use them in some way, to accommodate these we've built in a host of spare fields in the FM db.  The fields & titles we get and put into these spares changes all the time so I'd like to be able to run a find across multiple fields and return the FM field name of the match.

Say for instance the client say's "use the '2012 Race Title' field we supplied you this time"  - I look at all of their 100 or so field headers we imported into filemaker, I find it in FM field 'Spare57' and then move it to a more relevant place.  Done, but this is manual and too slow, if I could run a find for '2012 Race Title' and FM pops up with Spare57 as the result it would save an awful lot of time!

All comments appreciated,

Lee

[comment]

The question is not well-defined enough, because - at least in theory - you could find 10 records containing the search phrase, each in a different field. And some of the records could contain it in more than one field.

You could certainly loop among the fields of the record until you find the first one that contains the search phrase (or test all of them), but the result will be applicable only to the current record.

---
As an aside, there's probably a better method to organize this data - but I am not sure I fully understood the problem.

Edited June 10, 2015 by comment

[dwdata]

Don't think you are going to get that functionality from the Quick Find in any FMP version (unless someone has figured a clever was to do this). 

Most likely you will have to script entree find to track where you are actually getting a found set result.

Check this little file a mocked up...

Don Wieland
DW Data Concepts

QuickFindwResults.zip

Edited June 10, 2015 by dwdata

[William Slim]

Comment & dwdata, thanks for your responses.  In fact I do only want to scan fields from the first record!  ie the header of the imported set.  I'm not attached to using Quick Find it just happens that this function does half of what I want to achieve.

Sorry I wasn't clear before, let me try again  -  Say for arguments sake our DB has 40 agreed/predefined meaningful fields and 50+ fields that are always in flux. In Filemaker we've created & labelled the 40 agreed fields as appropriate, Name, Address etc.  The other 50+ fields are simply labelled Spare01 to Spare50 because what will be imported into them will always fluctuate.  

The client sends us 10k records which we import into our FM DB.  Later the client says "Use fields x, y, z from the data to do such and such on this occasion"  ...using the 1st header record I manually search among the 50+ spare fields for headers that match x, y & z, once found I move them to fields more appropriate for purpose.

We get lots of data supplies and most processing is automated but this still takes time, I was hoping to write a script that allows me to input the header name and it then searches the 50+ Spares of the header record and finds where the one I'm looking for resides, then the script can move it to the appropriate field.  We typically have three important fields:  ImportantField1, ImportantField2, ImportantField3  ...but we can never know which of the spares the important data will be found in because it always changes, the important data changes too.

Maybe looping through is the way to go, I'm guessing I'll need a layout that only contains the spares I want to loop through.  I'll have a bash at this today.

[William Slim]

Thanks for the file dwdata, that's pretty cool and I think I can use that for another solution to a different task.

[dwdata]

Thanks for the file dwdata, that's pretty cool and I think I can use that for another solution to a different task.

​So you want an automated way to search for a value in your spare fields and take that value and move to another targeted field. You want to be able to define the search values and its target field prior to running the routine.

Am i leaving anything out?

Don Wieland
DW Data Concepts

 

[comment]

So it seems your question is really this: how do I find which field of the current record contains the search phrase?

The simplest way to do this, IMHO, is to have a layout that contains all the "suspect" fields, and either remove all other fields or take them out of the tab order. Then run a script like:

Set Variable [ $searchPhrase; ... ] 
# 
Go to Layout [ “AllFields” ] 
Go to Next Field 
Set Variable [ $firstField; Value:Get ( ActiveFieldName ) ] 
Loop 
 If [ Get ( ActiveFieldContents ) = $searchPhrase  ] 
  Show Custom Dialog [ Message: Get ( ActiveFieldName ) ] 
  Exit Script [  ] 
 Else 
  Go to Next Field 
  Exit Loop If [ $firstField = Get ( ActiveFieldName ) ] 
 End If 
End Loop 

Alternatively, if the "suspect" fields are named predictably such as "Spare01", "Spare02", etc. you could do this directly at data level, for example:

Set Variable [ $searchPhrase; ... ] 
Set Variable [ $fieldName; Value:"Spare00" ] 
# 
Loop 
 Set Variable [ $fieldName; Value:SerialIncrement ( $fieldName ; 1 ) ] 
 Exit Loop If [ EvaluationError ( GetField ( $fieldName ) ) ] 
 If [ GetField ( $fieldName ) = $searchPhrase ] 
  Show Custom Dialog [ Message: $fieldName ] 
  Exit Script [  ] 
 End If 
End Loop 

 

[William Slim]

Yeah pretty much, but not just move a value, rather the whole field and all contents; let's say a field called 'Start Date' was imported into Spare52 in FM, I get instruction that 'Start Date' needs to be in ImportantField1, I want to hit a button type in 'Start Date' the script finds which FM field that's in (Spare52) then Show All records and move the entire contents to ImportantField1.

Note:  Because the data we import comes from other systems, we always have a header record.  So I'll search for 'Start Date' on the header record and all other records will actually have dates in that field.

I can't think of an easier method other than writing a long IF & Else IF script... using a variable for the user input to match on

[comment]

Once you have captured the required field name ("Spare52" in your example) in the $fieldName variable , you can (1) find the records which need to be affected by this, (2) go to the field you want to be modified (ImportantField1 in your example), and (3) replace the field's contents with a calculated value =

GetField ( $fieldName )

Be careful with this, because it's really easy to mess up and there's no undo.

 

Note that the above requires the target field to be on the current layout. Alternatively, you could loop among the records that need to be modified, and do Set Field By Name[].

Edited June 11, 2015 by comment

[dwdata]

If the goal is to search for a value in your "spare" fields and move that values to a defined "real" fields, I have modified my sample file a bit. Check it out and see if it fits your needs. If not, please specify  where it falls short.

It does use LOOPING which on a large found set will chug away, but definitely faster than manually doing it. Oh if we only had the Replace Field Contents By Name function ;oP

Let me know!

FindValuenMigrate.zip

[David Jondreau]

When doing imports, I usually use several tables. At least one for grabbing the data, and another for the final product. You do your "data transforms" in a later table. 

It's hard without seeing the data, but I'd say you want the table you have now ("raw data"), a "mapping" table, and the "final data" table you do the actual import into your live data file.

You import the data into the "raw data" table. Run a script that fills out the "mapping" table. That will be table with all the field names in your "raw data" table. Each record will be a field name in the "raw data" table that is set by script (that loops through the first record and reports back the field names based on the headers).

The "final data" table will be calcs that look to the mapping table to pull the correct fields from the raw data table.

Sorry if this is a little abstract. One benefit of having several tables means you don't overwrite important data.

 

 

[William Slim]

It's been a crazy few weeks for me so this has been on hold until now but I've been reading back through this and realised that my problem description could have been a lot more precise & simple, yet guys your responses, wow, above and beyond!  Thank you so much.  I have considered the solutions here and now realise I can almost definitely achieve this in more ways than one using your ideas.

I'll report back with some notes on the solution I use   -  Thanks again!