Jump to content
Sign in to follow this  
William Slim

Return Field Name with Quick Find?

Recommended Posts

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

Share this post


Link to post
Share on other sites

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 by comment

Share this post


Link to post
Share on other sites

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 by dwdata

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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

 

Share this post


Link to post
Share on other sites

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 

 

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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 ( $eldName )

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 by comment

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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.

 

 

Share this post


Link to post
Share on other sites

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!

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Sign in to follow this  

  • Similar Content

    • By TasKaras
      I am unable to find one specific record using Quick Find, but can find this same record using a standard field search.  I can find all other records that I search for using Quick Find.
      Any suggestion for a fix please?
    • By Mountain
      How can I return the actual name of a field in a calculation?
      If the field is "guarantee"
      to return the text "guarantee"
      Thanks
    • By Chong-Yee
      Hi, Everyone,

      Is there any way to use FileMaker's Internal SQL on a solution, where the names of the fields contain spaces?

      Statements like:

      SELECT Last Name from People WHERE Team Role = 'Judge'

      or even

      SELECT zzID from People WHERE Last Name = 'Tan'

      fails spectacularly - I think because the SELECT clause expects a comma delimited list of field names. Here, the field names in my solution use spaces (for legibility - and FM does not complain when I do that).

      I have tried SELECT 'Last Name' but that only gives the literal in every row.

      The FM ODBC/JDBC Guide is not much help. All it says on page 47 (Field Names) is

      "The most common expression is a simple field name, such as calc or Sales_Data.Invoice_ID." (thanks!)

      It should't make any difference, but I am using the BaseElement plugin 1.2.1 to send the query to FileMaker.

      Any help would be gratefully appreciated. Thank you in advance.

      Regards,

      Chong-Yee
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.