1 2 Previous Next 22 Replies Latest reply on Mar 3, 2009 4:41 PM by johnhorner

    GLOBAL fields as find criteria

    synergy46

      Title

      GLOBAL fields as find criteria

      Post

      I created 2 global fields:

       

      monthh  (text)

      yearr (text)

       

      When I construct a FIND using the Find Diaog, the monthh and yearr fields are grayed out / disabled.  If I remove their 'global' characteristics, they are selectable.

       

      What I am trying to do is to  give the user a 'popup' list of months and a text edit of year.  Then I produce a report where the month and year of DATE (date) are produced.

       

      Anyone have any ideas?

       

      Thanks

       

      Ron

        • 1. Re: GLOBAL fields as find criteria
          johnhorner
            

          hi ron,

           

          i'm not sure if i compltely understand how your database is configured and what exactly you are trying t do but i think i might have an idea for you.  i am assuming that apart from the global monthh and yearr fields that you are using to define your search criteria, you also have either a non-global set of month and year fields or simply a date field for each record.  if this is correct, you could use pop-up lists for monthh and yearrand set up a script trigger so that after you select from the popup menu it will execute a script that does essentially the following:

           

          enter find mode

          setfield [month; monthh)

          setfield [year; yearr]

          perform find

           

          if you are only using a date field for your records it is still the same idea but you would need to either add a step into your script to create a global datee field from monthh and yearr to use in the date field(eg, setfield [date; datee]) or you would need to create month and year calculation fields (non-global, eg. month=monthname(date)) so that you could use the script above.

           

          does that help?  i hope so.  good luck.

           

          john h.

          • 2. Re: GLOBAL fields as find criteria
            comment_1
              

            synergy46 wrote:

            What I am trying to do is to give the user a 'popup' list of months and a text edit of year.


            That's fine - just give it to them while in Browse mode. Global fields cannot be entered in Find mode, because they cannot be searched. Once you have their input, go into Find mode and set the appropriate fields to the global values.


            • 3. Re: GLOBAL fields as find criteria
              synergy46
                

              Thanks for the reply.  I understand how you didn't 'get' my explanation.  I was trying to keep it brief and simplistic.   So, here is the 'whole thing'....

               

              This is what I am trying to produce: http://ronaldogordono.googlepages.com/quizpage

               

               

               

              I have  table called History

              Each record has these fields:

               

                 Event  (will be the break field in a sub-summary report)

                     Full Name  (text)

                     Datee (date)

                     Monthdate (calculated field that pulls the 3 digit month from Datee)

                     Yeardate (calculated field that pulls the right(4 digit year from Datee)

               

              Some events in the History::Events field have names preceeded by a *.  These names are 'found' and become the Events in the Sub-Summary report.

              Everything works well at this point.

               

               I produce my sub-summary report from this script:

                   Got to Layout ["Form 13" (history)]

                   Show all records

                   Perform Find [history::event="*"

                   Sort Records [restorre]  // sort on Event

               Everything works well.

               

              However, in the Header of the report I have 2 fields:

                  monthh 

                  yearr

               

              I originally thought these 2 fields should be global.  They are to be fields that the user types in the Month (Jan, Feb etc) and Year (2008, 2009 etc)

              which will limit what records show in the report.

               

              **** I am missing something in your suggestion about Setfield.  I tried to add a Setfield step in the script but when I do it complains that "the field is not modifiable" ??  But, if I do a manual 'search' and enter Feb into the monthdate field, it works!???  

               

              Thanks for reading... and trying...

               

              Ron:smileyhappy:

              • 4. Re: GLOBAL fields as find criteria
                johnhorner
                  

                hey ron,

                 

                i knew that you can't search a global field but perhaps you can't even access it iin find mode.  that is not a problem.  t just means adding a couple of steps to your script.  before in your script, before you enter find mode, you will need to create 2 variables to temporarily hold the contents from the global fields using the following steps:

                 

                setvariable [$month;monthh]

                setvariable [$year ; year]

                 

                 then you would continue with the script essentially as you had it before except instead of using the global fields in the setfield steps, you would use the variables:

                 

                enter find mode

                setfield [month; $month)

                setfield [year; $year]

                perform find

                 

                i think that should work.  if not, let me know and there are still other options we can investigate.

                 

                keep me posted,

                 

                john h.

                 

                 

                 

                • 5. Re: GLOBAL fields as find criteria
                  comment_1
                    

                  That is hardly necessary:

                   

                  Set Field [FieldToSearch ; GlobalField ] 

                   

                  is certainly possible in Find mode. 

                  • 6. Re: GLOBAL fields as find criteria
                    synergy46
                      

                    Thank you for the reply.  Please notice that I need to do 2  'finds':

                    (you can see the desired output here)

                     

                    #1 lists ONLY those Events whose name starts with *.

                    then

                    #2 lists the * Events that have a monthdate  = monthh and yeardate = yearr (yeardate is a calculated field that produce Jan, Feb ... and  yeardate is just an editbox where the user types 2009, 2008 etc....  Also, monthh and yearr are GLOBAL.

                     

                    I will try your suggested code.   

                     

                    Ron

                    • 7. Re: GLOBAL fields as find criteria
                      synergy46
                        

                      (you can see the desired output here)

                       

                      My script reads:

                       Got to Layout ["Form 13" (history))]

                      Enter Browse Mode ()

                      Show All Records // probably not necessary

                       Set variable [$month; value:"monthh"]  // Why the "" for a field reference?

                      Enter find mode []

                      Set field [history::monthdate; $month]

                      perform Find [restore]

                      sort records [Restore; No dialog] // Sort to produce sub-summary on Event

                       

                      Got any ideas?  :smileysurprised:

                       

                       

                      • 8. Re: GLOBAL fields as find criteria
                        comment_1
                           Perform Find [Restore] will restore stored find requests - erasing everything you have done since entering Find mode. If you you want to add stored criteria to your request, you should so at the beginning, with the Enter Find Mode step:

                        Go to Layout ["Form 13" (history)]
                        Enter Find Mode [Restore]
                        Set Field [history::monthdate; history::monthh]
                        Set Field [history::yeardate; history::yearr]
                        Perform Find []




                        • 9. Re: GLOBAL fields as find criteria
                          synergy46
                            

                          Thanks.

                          The problem is that I can not do a find on Event="*" and then do another find where monthdate=monthh.

                          When I do this, it 'kills' the Event find.  

                           

                          I did not know that using Find [restore] wiped out my previous find so I will try and work on a find that does not do that.  

                           

                          I appreciate your ideas.

                           

                          Ron

                          • 10. Re: GLOBAL fields as find criteria
                            johnhorner
                              

                            hey ron,

                             

                            comment is correct.  i did an experiment and you should be able to use the setfield script step:

                             

                            setfield [month ; monthh ]

                             

                            although you cannot search within a global field, you can access it in find mode to pull the data out of it.  so if you are getting an error during the execution of your script it is coming from somewhere else (at least this is true for version 10 which i tested this on).  i think i most often see that [articular error when i try to modify a calculation field.  make sure that monthh is set as a global text field, and yearr is set as a global number field.  you should also be sure that the in the field options you have it set to "allow user to override during data entry" in the validations panel (this is the default so it should already be set that way).

                             

                            anyway, for the sake of getting this to work, let's forget that for a minute and stick with the method we were trying use that relies on establishing variables. i forgot that you were trying to narrow down a previously found set.  in this case what you need to do is make one small change.  you need to replace the "perform find" step with a "constrain found set" step and leave the "specify find requests" checkbox unchecked.  this will search only the records in the current found set rather than search all the records in teh database.  i am assuming that you want only records that match both the user entered monthh and the yearr.  if you want it to show all the records that match either the month or the year you will need ot set it up a little differently, but it is still easily accomplished.  let me know if this is the case and i will offer slightly different solution. 

                             

                            hope this helps,

                             

                            john h.

                            • 11. Re: GLOBAL fields as find criteria
                              comment_1
                                

                              synergy46 wrote:

                              The problem is that I can not do a find on Event="*" and then do another find where monthdate=monthh.

                              When I do this, it 'kills' the Event find.  


                              I didn't know you were doing "another find". Although you could certainly do a Constrain Found Set[] instead of Perform Find[] , I don't see why you cannot enter all of your criteria into the same request and find what you need all at once:

                               

                              Go to Layout ["Form 13" (history)]
                              Enter Find Mode []
                              Set Field [history::monthdate; history::monthh]
                              Set Field [history::yeardate; history::yearr]

                              Set Field [history::Event; Quote ( "*" ) ]
                              Perform Find []
                               

                              Note that no requests are restored here.


                              • 12. Re: GLOBAL fields as find criteria
                                synergy46
                                  

                                 

                                Thanks.  It works like a charm. I learned a *lot* from you and the other contributors on this forum.  What a resource!

                                 

                                In creating this script I somehow got FM to produce this:

                                 

                                 


                                Set field [history::event, quote ("*")]

                                // not complaining.  This works great!

                                 

                                // Just what does the 'quote' word mean?  And, it is noteworthy that history::event, "*" does not work.  I can not find any documention that explains the working 'quote' above.

                                 

                                Just wondering

                                 

                                Thanks a lot

                                 

                                Ron


                                 


                                • 13. Re: GLOBAL fields as find criteria
                                  comment_1
                                    

                                  Quote() puts quotation marks around the asterisk (actually, the function does a bit more, but it's not relevant here). I use Quote ( "*" ) as a more convenient (and readable) way to write

                                   

                                  "\"*\"" 

                                   

                                  Both return the same result. The asterisk must be quoted, otherwise it would be considered a  find operator.

                                   

                                   

                                  OFF TOPIC:

                                  Your method of marking some records by a leading asterisk is not good practice. A good rule of thumb to follow is "one fact per field". If these records have some distinguishing attribute, there should be a dedicated field for this purpose. A number field toggled between 1 and 0 (or empty) is a good way to permanently tag records with a true/false value.

                                  • 14. Re: GLOBAL fields as find criteria
                                    synergy46
                                      

                                    Here is what the report looks like:  <<CLICK HERE>>

                                     

                                    Here is a weird thing that just cropped up:"

                                    I have 2 scripts:

                                     

                                    #1 Loaded with the form:

                                    Enter Browse Mode

                                    View as List

                                    Show all records

                                    Enter find mode []

                                    set field [history::event; Quote("*")]

                                    perform find[]

                                    sort records [  history::event; ascendidng]   

                                     

                                    // this works great

                                     

                                    #2  activated by user to find a designated month (monthh) and year (yearr)   // both text fields

                                    Show all records

                                    enter find mode

                                    set field [history::event; Quote("*")]  // this is necessary to recreate the sub summary break on Event

                                    set field [history::monthDate; history::monthh] // works great and finds specified month

                                    set field [history::yearDate; history::yearr] // DOES NOT WORK. IT DOES NOTHING????

                                    Constrain found set[]

                                    Sort records [ history::event;ascending]

                                     

                                    First, it seems weird that after the sub-summary report (breaks on Event) is produced, the "find" button code  (#2 above) MUST respecify the set field[history::event; Quote("*")] or the report looses the sub-summary break.

                                     

                                    Second, History::yearr doesn't do anything??? I have set yearr and yeardate to both numeric.  It does nothing.  I have set them both to text.  They do nothing.  When I run the Debugger, monthdate gets the value from monthh but yeardate DOES NOT get the value from yearr?  Waz up with that!?

                                     

                                    Thanks

                                    Ron

                                    1 2 Previous Next