Performing Find on more than one field at a time.

[Jarvis]

Is it possible to search more than one field when performing a find?

What I would like to do is start with a string of text and then see if this particular string of text shows up in any of a half dozen fields?

Is this do-able or do I need to constrain my searches to one field at a time?

[doughemi]

Enter Find Mode[]
    Set Field[FirstField; "Your Search String"]
    New Record/Request
    Set Field[SecondField; "Your Search String"]
    //Etc.
Perform Find[]

This is what is known as an "OR" find, in which you search for the search object in FirstField or SecondField and so on for as many fields as you like. The steps in the above script can also be performed manually using the Add New Request command in the Requests menu in Find mode.

 

Edited July 6, 2015 by doughemi

[comment]

It is possible by using multiple requests (or even quick find). However, you should ask yourself why is it necessary to search "any of a half dozen fields" for the same content - as it suggests a possibly flawed structure.

[Jarvis]

Thanks Doughemi for the suggestion of "OR" find.  Have never used that before.

Comment,   I started to post this question two or three times but each time as I was composing it I went back to the drafting board to see if  I could think of a way to do this on my own or if there was a way to formulate the question to get the answer that I wanted.

I am building a task manager for my cabinetshop.  For simplicity I try to keep all fields germane to any particular cabinet within a single record.  The record, of course, references many tables as elegantly as my brain will allow.  (I am a cabinetmaking SME, not a professional developer.)  I am trying to build this with as low overhead possible with respect to download & upload speeds.  Most of the work takes place with SETFIELD scripts.

One of the SETFIELD scripts populates the fields:  TASK 1 - TASK 2 - TASK 3 etc.  If, for example, the cabinet was a sink base the script  would set field TASK 3 to "Build Cabinet Doors".  If the cabinet was a drawer bank then the script might set field TASK 3 to "Assemble Drawer boxes".  The point is that each type of cabinet has different types of tasks.

The logic behind keeping these as separate Task fields is that I can build a layout that displays a grid of tasks, much like a bingo board.  (I got this idea from an example file LaRetta posted several years ago about conditional formatting stadium seating to show which seats had been sold and which remain.)

This grid follows each cabinet through production.  Using script triggers (upon field entry)  to set the field contents to [""] essentially crosses the task off the bingo board each time we click into the field.  We this way get a graphical  representation of what is complete and what remains with these scripts.  Task management becomes more visual this way.

Using a SETFIELD script works for the standard task.  Cabinet gizmos, like tray dividers, silverware storage, recycle pullouts, vary from cabinet to cabinet.  Some cabinets might have one of these gizmos and some cabinets might have several of the gizmos.  Gizmos are selected from a radio button value list .  If there is more than than one gizmo I currently need to stipulate this as an additional task to have it show up in my bingo board matrix.  

Sometimes when manufacturing a cabinet we find it useful to see if any other cabinets share the same gizmo.  Because of the multiplicity of gizmo possibilities we might have to search several fields to see if they exist.

I hope this explanation is not more convoluted than my DB logic.

 

 

 

[comment]

One of the SETFIELD scripts populates the fields:  TASK 1 - TASK 2 - TASK 3 etc.  If, for example, the cabinet was a sink base the script  would set field TASK 3 to "Build Cabinet Doors".  If the cabinet was a drawer bank then the script might set field TASK 3 to "Assemble Drawer boxes".  The point is that each type of cabinet has different types of tasks.

Well, I cannot pretend I understood ALL of it, but this part is quite clear. And it's quite clear that instead of numbered fields (always a red flag) you should have a structure along these lines:

 

 

Edited July 6, 2015 by comment

[Mark Scott]

 

Hi Jarvis,

Comment's suggestion is, as always, spot-on.  In addition to the querying difficulties you've already encountered, another major problem with "Task 1," "Task 2," etc. is that you have to hard code a sufficient number of tasks fields to accommodate current and unknown future workflows.  If you decide, for example, that 8 tasks is sufficient to complete any type of cabinet, then there is a law of nature that says that your next new cabinet type will require 9 tasks.  Hard coding those things is not very extensible, whereas a normalized design in which the tasks are relegated to records in a related (1:M) table is absolutely extensible.

Mark

[Jarvis]

Thanks Mark & Comment,

I hadn't really considered using a related (1:M) table but will poke around with some SETFIELD scripts that do just this.    If I understand this correctly the script would create a new record in the related table, grab the cabinet ID number etc.

That would make it easier to also include some information about work stations attendant to the task as well as known train wrecks,  URL links to training videos etc.  Would be great to also create some kind of certification to vet workers comprehension of tasks etc.

The more I think about this the more I realize labor activities are merely associated with a cabinet style but really live in a world all their own.

[Mark Scott]

That would make it easier to also include some information about work stations attendant to the task as well as known train wrecks,  URL links to training videos etc.  Would be great to also create some kind of certification to vet workers comprehension of tasks etc.

The more I think about this the more I realize labor activities are merely associated with a cabinet style but really live in a world all their own.

I totally concur with your assessment, Jarvis  !  The ability to record additional information about each task would be reason #3 for going with a normalized design.

Let us know how it turns out.

Mark

[comment]

If I understand this correctly the script would create a new record in the related table, grab the cabinet ID number etc.

Possibly, but not necessarily. Another option is to create new records in the Task Status table only as needed, by entering the statuses in a portal to Tasks. But that is a little more difficult to set up than creating N new Task Status records for each new cabinet a priori.