Jump to content
Sign in to follow this  
rpatel

find and display

Recommended Posts

rpatel

Hi to everyone,

 

I need some help.

 

I have developed a patient database for the program i work with and am having difficulties with the following.

 

For the find: there are 20 different fields a user can choose from for their find. They may not choose all of field, but they have the choice of 20.

 

What I would like to do, is once the field is choosen and the find is complete, I would like the results to be viewed on a table view, with only the items the used to search with (this could included multiple criteria). Ideally I do not want all the tables to show which are related to those fields, just the fields.

 

#1: Is idea of mine even possible?

 

#2: if so, how do I do it?

 

Anyone and everyone's help would be appreciated, as I am a beginner to FM and to database development.

 

Thanks in advance.

:eek:

Share this post


Link to post
Share on other sites
SurferNate

Yes almost anything is possible, but you need to adjust your expectations based upon your ability and time available.

 

1) You can build a special "Find" layout with global fields which are linked to value lists. This limits the data that one can enter for their find request. One way to do this is to have a special table called "Find" and have fields in that table which are only used to enter find data. Then you have a script attached to a button which goes to the special "results" layout and enters all of the "find" data and then performs a find.

 

Issues to consider:

 

If you have 20 fields and you only want the user to see the fields which they used for their find request in the results, I am not sure how that could be done (though I am sure there is some way even if it is convoluted and counterintuitive). You would be better to build one or more "result" layouts which the script would choose from based upon the find request.

 

Maybe, just maybe, it could be done with a set of complex relationships. Again, the structure would be fairly complex but you could build a table of calculated fields that generate results based upon the criteria entered into match fields.

 

Darn, I just got intrigued, I'm going to try it out now with a portal/relationship setup. I think I have an idea how to do it...

 

EDIT: The light bulb just went on! This not a "find" problem at all but a self join situation. A self join showing a portal back to the same table based upon global match fields would do the trick.

 

This involves two lessons which I myself am not fluent in just yet.

 

1) You need a filtered portal. Study dynamic portal filtering. There are great resources available. Search this site for links.

 

2) IN the filtered portal, you need calculated fields that show data based upon the contents of the global match fields. You can do this with a repeating calc field like this:

 

DisplayField is a repeating calculation field with

 

DisplayField =

Let([

R = Get(CalculationRepetitionNumber) - 1) ;

G1 = Extend(gFindField1) ;

G2 = Extend(gFindField2) ;

G3 = Extend(gFindField3) ;

F1 = Extend(Field1) ;

F2 = Extend(Field2) ;

F3 = Extend(Field3) ] ;

 

Choose(R ;

Case( G1 = "Field1" ; F1 ; G1 = "Field2" ; F2 ; G1 = "Field3"; F3) ;

Case( G2 = "Field1" ; F1 ; G2 = "Field2" ; F2 ; G2 = "Field3"; F3) ;

Case( G3 = "Field1" ; F1 ; G3 = "Field2" ; F2 ; G3 = "Field3"; F3) ;

)

)

 

 

The calc is valid, there is a WHOLE lot more to the lesson though, naturally, I am sure there is a better way to do this.

Share this post


Link to post
Share on other sites
Maarten Witberg

I think portal filtering is not going to work in this case.

A filter in this case would mean concatenating twenty fields on both ends of the relation. The filter would then compare these two calcs, but fm has indexing limits for relationships. In fm7, if I understand the helper correctly, this indexing is limited to the first 100 characters of a field. This seems a lot (5 times as much as version 6) but still, if the user enters, say ten search fields with twelve characters each his search would yield untrustworthy results.

Second, because portal filtering requires exact field matches, you'd need to rely on value lists for field entry. This is practical for limited number of variables per field only.

 

The idea of a display field is good though. To keep overhead small, I'd suggest a find script that sets the display field in a loop.

 

Will get back to you with a suggestion for this.

 

kjoe

smirk.gif

Share this post


Link to post
Share on other sites
Maarten Witberg

There's another consideration and that is the question why would you only want to display the fields that match the search? I'd expect the user to be curious about the field content of the other fields... f.i. if in a contacts list I search for "jones" I'd want to know his phone number... the mere fact that he's in the file is not very interesting.

Displaying only the matching fields seems a lot of hassle when all you can say of the found set then is the found count.

Unless you'd want to do an OR search and look for patterns in the found set.

 

(to be continued... laugh.gif )

 

kjoe

smile.gif

Share this post


Link to post
Share on other sites
Maarten Witberg

oh yes. an AND search would be interesting to display in this manner if the field content would differ... f.i. search for "jones" and find "catherine zita jones" AND "indiana jones". hmmm. now there's an odd couple....

 

duty calls....later....

 

kjoe

smile.gif

Share this post


Link to post
Share on other sites
aaa

Kjoe is truth: "why would you only want to display the fields that match the search?"

Other question is to display in layout only fields which user was choosing. Who know can we do it in v6 or in v7?

Share this post


Link to post
Share on other sites
rpatel

laugh.gif

 

 

Thanks to everyone who replied.. i am going to go back to the drawing board on how to do the search.

Share this post


Link to post
Share on other sites
Maarten Witberg

hi rpatel,

 

please tell us what you are trying to achieve. I'm pretty sure we can help you.

 

kjoe

smile.gif

Share this post


Link to post
Share on other sites
SurferNate

Somehow I knew there would be a catch.

 

I was trying to figure it out mostly for the pleasure of the challenge. I did not think of the limitations of the program. I also could not figure out why one would only want to see the fields that match the search fields. It would be easy enough to just generate a count with a relationship.

 

What I did read into the request was that a maximum number of "requests" would be applicable, which would be far under the 20 that would be available (assume five or so?). That assumption made it a different ball game.

Share this post


Link to post
Share on other sites
Maarten Witberg
What I did read into the request was that a maximum number of "requests" would be applicable, which would be far under the 20 that would be available (assume five or so?).
I think 98% of the cases, that would be the case. Only, I think if you give the user the opportunity to enter 20 search criteria, then you should make sure your system is up to the challenge. Because those 2% will occur sooner or later.

 

Still, even when the number of possible criteria is limited, filtered searching requires exact matches otherwise it will yield no result. f.i. searching for "jones" would only give fields that contain exactly the term "jones" and not, "jonesy", "jonestown" etc. which would be a major drawback.

 

What remains is rpatels question, an OR search I still think could be useful, and aaa's.

 

kjoe

smile.gif

Share this post


Link to post
Share on other sites
rpatel

Wow i am impressed with everyone. I didn't realize that this complex problem for me (a beginner to db development) was even more complex.

 

I think I have decided to place only 20 fields into a search layout, and from there, they can perform searches.

 

the results will show up, like they alway's do, but what I am thinking now is to have the words they used highlighted.

 

Like when you use the 'find' for example on when you are finding something in a document, it highlights the word.

 

Now, i realize this would still be complex for my level of knowledge with respect to FM, but is it tooo complex to do? or do other people have better ideas on how to design search layouts? I would love the help.

 

Background on the db:

 

Patient database, which will store about 5,000 and more patient over the past 30years and still continuing. I am using FM 7 (not developer)

 

Thanks

rp.

Share this post


Link to post
Share on other sites
SurferNate
Originally posted by kjoe:

Still, even when the number of possible criteria is limited, filtered searching requires exact matches otherwise it will yield no result. f.i. searching for "jones" would only give fields that contain exactly the term "jones" and not, "jonesy", "jonestown" etc. which would be a major drawback.

 

What remains is rpatels question, an OR search I still think could be useful, and aaa's.

 

kjoe

smile.gif [/QB]

There is actually a trick I applied recently in which a created a calculated match field that built a list of partial words etc from the contents of multiple source fields. Specifically, I used it to instantly filter a large list of contacts. Therefore if a person entered "bob" into the 'gFind' field the relationship would return records for Bob Fletcher, Bobby Mathis, James Bobb, and Sally Smith at Bobrick Company. Of course, out of sheer blind luck, the match field in my case did not exceed 100 characters but it was an interesting workaround. Naturally it still requires some restraint or control over the user entry. It's not nearly as slick as a dynamic find like in iTunes or Entourage.

 

Hmmm...thinking of it, there may be some way to apply Clairvoyance here. I still haven't had time to learn that yet.

Share this post


Link to post
Share on other sites
SurferNate

The thing about this whole thread is that it indirectly involves some of the limitations in FM7 for which workarounds must be invented. That's why I am so intrigued.

 

 

Limitations such as:

No Typeahead

No dynamic layout generation

 

Also I am set back by the inability to turn a whole layout 90 degrees and switch rows to columns.

 

 

:-)

Share this post


Link to post
Share on other sites
Maarten Witberg
There is actually a trick I applied recently in which a created a calculated match field that built a list of partial words etc from the contents of multiple source fields. Specifically, I used it to instantly filter a large list of contacts.
that's an interesting trick. how did you do that?

 

Also I am set back by the inability to turn a whole layout 90 degrees and switch rows to columns.
What I did for a project planning file was this:

-users generate a weekly planning directly from the main projects management file (hours per employee per week per project) (either manually: in week 1, 2 days, in week 2, 2.5 etc, or a script that enters, say, 2 days a week from week 12 till week 20.)

this gives a line items file that can be viewed in the project management file. But this is not enough to create a cross-the-board planning overview that can be edited using data such as workdays per employee, workload and so on.

 

So to create a quarterly planning overview per employee I made a second file. The main part of yhe relationship filter was a calc that determined in which quarter a week falls. So a line item in this file consisted of one quarter with thirteen fields, one for each week in the quarter (so actually, thirteen relationships). The second part of the filter was for projects and employees, to be able to generate a quarterly line item per project per employee.

So was I able to turn weekitems 90 degrees.

 

anyway, I was sent back to the drawing board because the main user insisted on a free choice of starting week, not 1-14-27-40.... :eek: ... still pondering how to do that in an easy way.....

 

kjoe

smile.gif

Share this post


Link to post
Share on other sites
Maarten Witberg

for what it's worth, here's a sample tested first shot at an answer to rpatels original question, applied to an OR search.

 

1. define a global, one for each of the number of query fields you allow (used for field selection). name them g_query1, g_query2 etc..

2. define as many globals, for user entry of the search terms

3. define a calc field field_names = FieldNames(yourtable, yourlayout)

4. define a value list based on values in this field

5. stick the value list to each query global

 

script like this:

#
set field[your_table::counter;1]
loop
#test for empty queries
if[not IsEmpty(GetField("your_table::g_query"&your_table::counter) and not IsEmpty(GetField("your_table::g_search_term"&your_table::counter)]
#execute the query
enter find mode[ ] 
go to field[your_layout::first_field_on_the_layout]
 loop
   if[Get(activefieldname)=GetField("your_table::g_query"&your_table::counter)]
      set field[your_table::g_search_term1]
   end if
  exit loop if [Get(activefieldname)="last_field_on_the_layout"]
  go to next field   
 end loop 
#this bit determines it's an OR search...
if[your_table::counter=1]
 perform find[ ]
else
 expand found set[ ]
end if
#
#go to the next query
end if
set field[your_table::counter; your_table::counter+1]
exit loop if[your_table::counter>20] #or as high as the number of allowed queries
end loop
#
#put results in display field#
#
sort[restore, no dialog] #your preferred sort
go to record[first]
set field[your_table::counter;1]
loop
if[not IsEmpty (getfield("your_table::g_query"&your_table::counter)
   insert calculated result[your_table::display_field; GetField("your_table::g_query"&your_table::counter 
      &": " &GetField(GetField("your_table::g_query"&your_table::counter))&";¶"
end if
go to record[next, exit after last]
set field[your_table::counter; your_table::counter+1]
end loop 

it should be noted that 1) the display field is editable but the original fields are not related so they will not be affected by changes in the display field

2) additional searches will be appended to the field indefinetly. A script step "replace contents" (with null or "") at the start or performed on the found set is possible

3) this is my first shot at a v7 script, so bear with me please for inefficiencies or mistakes, either in the code or translation of same from dutch to english

4) i have tested for text fields and text globals only. I cannot say for sure what the results would be if a query (text) global would be used for searching a number field.

5) edited after this :rolleyes: http://www.maclane.com/cgi-bin/ultimatebb.cgi?/ubb/get_topic/f/16/t/000751/p/1.html#000001

 

 

kjoe

smile.gif

Share this post


Link to post
Share on other sites
SurferNate

Kjoe,

Here's a version of the calc. My gift to everyone. Note that I filter out random characters EXCEPT the spaces which are required for picking up additional matchable words in the fields. In this case only the first word is "chopped up" into pieces. If you added a level of complexity you could "chop up" additional words that may appear in each of the referenced fields. Also, I did not bother to shorten it any farther than the first "Let" statement. That could also be done.

 

Let(

[

F = Filter(FirstName ; " abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWZYZ") ;

L = Filter(LastName ; " abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWZYZ") ;

B = Filter(Business ; " abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWZYZ")

] ;

 

Case(

F = true or L = true or B = true;

Case( Length(F) >= 1 ; Substitute (Left(F; 1) ; " " ; ¶) & ¶) &

Case( Length(L) >= 1 ; Substitute (Left(L; 1) ; " " ; ¶) & ¶ ) &

Case( Length(B) >= 1 ; Substitute (Left(B; 1) ; " " ; ¶) & ¶ ) &

Case( Length(F) >= 2 ; Substitute (Left(F; 2) ; " " ; ¶) & ¶ ) &

Case( Length(L) >= 2 ; Substitute (Left(L; 2) ; " " ; ¶) & ¶ ) &

Case( Length(B) >= 2 ; Substitute (Left(B; 2) ; " " ; ¶) & ¶ ) &

Case( Length(F) >= 3 ; Substitute (Left(F; 3) ; " " ; ¶) & ¶ ) &

Case( Length(L) >= 3 ; Substitute (Left(B; 3) ; " " ; ¶) & ¶ ) &

Case( Length(B) >= 3 ; Substitute (Left(L; 3) ; " " ; ¶) & ¶ ) &

Case( Length(F) >= 4 ; Substitute (Left(F; 4) ; " " ; ¶) & ¶ ) &

Case( Length(L) >= 4 ; Substitute (Left(L; 4) ; " " ; ¶) & ¶ ) &

Case( Length(B) >= 4 ; Substitute (Left(B; 4) ; " " ; ¶) & ¶ ) &

Case( Length(F) >= 5 ; Substitute (F ; " " ; ¶) & ¶) &

Case( Length(L) >= 5 ; Substitute (L ; " " ; ¶) & ¶) &

Case( Length(B) >= 5 ; Substitute (B ; " " ; ¶) )

)

)

 

P.S. - If anyone improves upon this calc at all, please post a copy so we can all learn from it. This is, by far, not the best trick around. It's a bit of a kludge in my opinion but it works.

 

:-)

Nate

  

Share this post


Link to post
Share on other sites
SurferNate

Also, field validation is a good way to prevent input in more than a certain number of fields. You could have a field named

 

FindRequests =

 

Count(gFind1; gFind2 ; gFind3 ; gFind4 ; gFind5 ; gFind6)

 

Then your validation calc in all 6 gFind fields could be:

 

Case(FindRequests >4; True; False)

Share this post


Link to post
Share on other sites
Maarten Witberg

Nate, thanks for sharing that filter trick. very nifty indeed smile.gifsmile.gif

I don't really understand though why you include the case(length() statements. I stripped them and it works just fine, unless I am missing something.

What you did is build a multikey. I think the 100 character indexing limit applies to each line in the key not the full length of the key.

a second thing i don't understand is why the case(f or l or b) is there. Not up to speed with this Let[] part I'm afraid, it's to do with that? It doesn't work without. Furthermore, I had to reverse it to make it work: case not(f or l or b) confused.gif

 

Let( 
[ 
F = Filter(firstname ; " abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWZYZ") ; 
L = Filter(lastname ; " abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWZYZ") ; 
B = Filter(business ; " abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWZYZ")
] ;
Case(
not(F  or L  or B);
Substitute (Left(F; 1) ; " " ; ¶) & ¶ & 
Substitute (Left(L; 1) ; " " ; ¶) & ¶ & 
Substitute (Left(B; 1) ; " " ; ¶) & ¶ & 
Substitute (Left(F; 2) ; " " ; ¶) & ¶ & 
Substitute (Left(L; 2) ; " " ; ¶) & ¶ & 
Substitute (Left(B; 2) ; " " ; ¶) & ¶ & 
Substitute (Left(F; 3) ; " " ; ¶) & ¶ & 
Substitute (Left(B; 3) ; " " ; ¶) & ¶ & 
Substitute (Left(L; 3) ; " " ; ¶) & ¶ & 
Substitute (Left(F; 4) ; " " ; ¶) & ¶ & 
Substitute (Left(L; 4) ; " " ; ¶) & ¶ & 
Substitute (Left(B; 4) ; " " ; ¶) & ¶ & 
Substitute (F ; " " ; ¶) & ¶ & 
Substitute (L ; " " ; ¶) & ¶ & 
Substitute (B ; " " ; ¶ )
)
)

it's sort of three threads in one this one... sorry for any confusion people laugh.gif

 

kjoe

smile.gif

Share this post


Link to post
Share on other sites
SurferNate

Ahhh...therein lies the funny bit.

 

I had to build this as a text field that auto enters. Why? Because in the result portal I wanted a user to be able to enter a NEW contact frm the same layout. If the field is a calc field then it will not allow creation of new records via the relationship ;-). You need the case statement to trigger refresh of the multikey field calc.

 

The case(Length(field) > x) statement helps prevent redundancy inthe result. It stops getting the contents of a field when the content length has been exhausted already earlier in the calc.

 

The Let statement allows me to shorten the big text filters into a simple one character entry in the calc.

 

Also, the "does not equal" symbol will not display on this website. The calc actually is Case(F 'does not equal' "" ....). I assumed that the boolean version would also work. For some reason though, it does not always work when I think it should. confused.gif

 

Again, I could trim/adjust the calc more but this was easy enough to generate in about 15 minutes including the fields and the test data.

smile.gif

Share this post


Link to post
Share on other sites
Maarten Witberg
Also, the "does not equal" symbol will not display on this website.
yes, a pity. You can use =/= as a workaround.

 

kjoe

smile.gif

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.

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  



×
×
  • Create New...

Important Information

Terms of Use