Searching in multiple databases

[dcecil]

I have 2 separate databases that have unique records which represent our CM Library. These databases have to remain separate. When someone comes to me to get a particular item, I have to search one database and if it's not in there, I have to do the search in the other database. Is there an easier way? Can I have a field that would query each database? If so, how? This database is about 5 years old, so I don't want to redesign anything.

Thanks,

dcecil

[Søren Dyhr]

Are the finds made of stacked requests ...or just one single???

--sd

[dcecil]

Just single requests. I just want to put in a number or word and search both databases.

[Søren Dyhr]

Phew! That took a bit of the complexity out of the approach. Alright we're on Windows so Applescript is out of the question, to mend what actually is a fix to a bad table structure.

The tables should have been unified into one single ...that being said, is it sometimes interesting to be challenged into making an engineered solution, where you deliberately are playing with several handicaps. There must in this case under no circumstances be tampered with the files, except for instance some scripting - I hope!

Before we dwelve into the scripting, must identical field numbers be exisiting in both layouts in the two tables, that include also the tab-order. Since the file(s) can't be tampered with can't global fields be utilized either ...and being on FM7 means $ and $$ varialbes haven't found their way into the package yet. What it left then??? Well not much except perhaps scriptparamters and recursive scripting.

Imagine that we just have made a request in one of the tables, instead of hitting the "find" button in the status area do we execute this script:

If [ Get ( WindowMode ) = 1 ] 

    Set Error Capture [ On ] 

    Perform Find [  ] 

    Set Error Capture [ Off ] 

    If [  not Get ( FoundCount ) ] 

         Modify Last Find 

         Go to Field [  ] [ Select/perform ] 

         Go to Next Field 

         Perform Script [ “TraverseBackForward” ] 

    End If 

    Perform Find [  ] 

End If 

What it does is that it tries to find a record in the first table, and if it fails,  should the previous request be picked up, so it can be reused in the other table. How is it done??

If [ PatternCount ( Get ( ScriptParameter ) ; "|" )  <  ValueCount(FieldNames ( Get ( FileName ) ; Get ( LayoutName ))) ] 

     Go to Previous Field 

     Perform Script [ “TraverseBackForward”; Parameter: Get ( ActiveFieldContents ) & "|" & Get ( ScriptParameter ) ] 

Else 

     Enter Browse Mode

     Go to Layout [ “SearchLayout2ND” (producttable2) ] 

     Enter Find Mode [  ] 

     Go to Field [  ] [ Select/perform ] 

     Go to Next Field 

End If If [  PatternCount ( Get ( ScriptParameter )  ; "|" ) > 0 ] 

     Set Field [ Left ( Get ( ScriptParameter ) ; Position ( Get ( ScriptParameter ) ; "|" ; 1 ; 1 )-1 ) ] 

     Go to Next Field 

End If 

This is a recursive script, which means that scriptparamters are stacked, and remain to exists until every cycle have been unwrapped again. Between the first "If...Else" statement are all field values collected from each field in "Modify Last Find" step, when it's done is there no further need for recursions. This leads the script into 2nd phase, which switches to the other table and turns it into findmode.

The 3rd part might be the most confusing because it's just out of the reach of the recursion ...or is it?? Well what comes after the recursions "Else...End If" has to consider, what's in the stack. If we have plucked values from 5 fields is it going to have 5 slightly different scriptparamter strings. This is the main reason why we gathered the values from the last field towards the first. So what remains is to take the stuff before the first pipe sign in each parameter string.

When thats done are we back in the first script, ready to make exact the same search in the second table.

I could have uploaded the template I made these scripts in, but when it comes to it is tough scripting usually a sign of bad filestructure ...and if I just uploaded it would there be a danger that you just used it, without considering how sane it is to do.

But it was an excellent challenge, my hunch told me that it could be done ...and it could!!!

--sd

[comment]

Why not use a third file, with a global field?

[Søren Dyhr]

I have thought of it, but were a little exited with the recusion idea ...I had to try it out! ...you know how eager I am not to use neither globals, repeating fields nor Copy/Paste

--sd

[comment]

Yes. Well, you know my motto - simplest is best.

[Søren Dyhr]

Well althoug you use a global fields is the simplest isn't the solution the simplest when it comes to it, the method I refere to is this:

http://www.filemakermagazine.com/modules.php?op=modload&name=News&file=article&sid=544&mode=thread&order=0&thold=0

...but if you have somethis smarter up your sleeves might it be interesting for us to examine??? When it comes to the Copy Record/Request isn't it of much ...reminding me of making french fries with a tennis racket or similar metaphors.

--sd

[comment]

The request is not for saved searches. It is for "a field that would query each database". If you put such field in a third file, and create file references to the other 2 files, you can treat the 2 old files as if they were tables in the new file.

Search2files.zip

[Søren Dyhr]

If you put such field in a third file, and create file references to the other 2 files, you can treat the 2 old files as if they were tables in the new file.

Well it's a valid interpretation, only this brings in a slight ripple on an otherwise smooth surface:

I just want to put in a number or word and search both databases.

...which must be either the warehousenumber or the given name, but what if one or both searchwords are truncated - say only partly remembered?? This will point in direction of a genuine request.

--sd

[comment]

It can all be scripted. Instead of guessing, I would wait for the poster to raise any issues he may have.

The point here is that the existing files are not modified in any way.