1 2 Previous Next 17 Replies Latest reply on Apr 25, 2013 8:12 AM by ChrisJones_1

    Finding records and printing them



      Finding records and printing them





           I'm struggling with a way in which to find some specific records in my database. I have a vehicle hire database with three tables (clients, destination and drivers) that a main form called journey extracts data from plus some other specific fields. All is working fine but when the users of the database do a search (or find) for a particular client or a clients phone number or a journey reference number I would like the DB to find their query via a button and script on the Main Journey form and then present it on another form so the users can either print it or view it. I have tried to work this out using scripting that goes into Find mode and then Perform Find (specifying the three users queries) but I can't get it to work, so I think I'm missing something. 


           I also have what I think is a related issue. I'm trying to find and print some specific records from this same database and I think I need to use this same scripting method. What I would like to do is find all records that have a Yes in the Print Journey field and print them, (which I've managed to do) but I need to print some from a particular range. (Which I can't figure out how to do) For example there might be 12000 records in the database and at any one time I need to print some specific records say, from 120 to 560 but only those marked with Yes in the Print Journey field. And the next time I might need to print records from 750 to 790 with the the same criteria. I'm pretty sure it's something to do with perform find but I can't work it out. I hope I've explained this OK and someone can help with this.

        • 1. Re: Finding records and printing them

               Start with this thread of scripted find examples: Scripted Find Examples

               If that is insufficient, break this down into more detail and try agian by using Post A Answer to post your reply.

          • 2. Re: Finding records and printing them




                 OK i've had a chance to have a look at the scripts and they've helped solve the first problem I had, albeit with a slight modification, not quite right but it works. However theres nothing that really applies to my second problem so I'll try to explain in more detail what it is I'm trying to do.


                 Every day new journeys are added to the DB Main Journey Form as clients phone or email the company and each journey consists of clients and their details, the destination where the client is going, times and dates and the driver and his details and given a journey reference number automatically using the serial number function. There are about 80 repeat journeys a week which are added and given the next sequential journey reference number. Each week I need to print these repeat records out as a batch and that are marked print "yes" and then give them to the various drivers so they can collect the client and take them to their destinations. 


                 So my problem is, how do I select that weeks repeats from the Main Journey Form say, from record number 200 to 295 but only print those records marked print YES and then do it all again the next week for the following weeks repeats from 395 to 455 marked YES and then display them on another form marked Printout? I've managed to get the first part working using the following script :


            Enter Find Mode [ ]

            Perform Find [ Specified Find Requests: Find Records; Criteria: Journey::Print Status: “Yes”
                 Find Records; Criteria: Journey::JourneyID: “#” AND Journey::Surname: “@” AND Journey::Phone Number: “#” ] [ Restore ]

                 This kind of works but I can't or don't know how to select the ones marked Yes. Any help would be greatly appreciated.

            • 3. Re: Finding records and printing them

                   Please note that your example does not match the examples in the thread that I suggested you examine. All of those scirpts avoid using any stored find criteria. Both Perform Find and enter find mode are listed in those examples with empty brackets. One of the reasons I don't do that is that calculations cannot be evaluated inside a stored request, but can be evaluated in a set field step.

                   If by "record number" you mean the serial Id, you can use the following expressions in set field steps in a scripted find:

                   Set Field [Journey::JourneyID ; Globals::gFilrstrecord & "..." & gLastrecord ]
                   Set Field [Journey::PrintStatus ; "Yes"]

                   You would then enter values such as 395 and 455 into gFirstrecord and gLastrecord before performing the script to find all records with ID's in that range that also have Yes in the PrintStatus field.

                   I'm not sure what you are trying to accomplish with the @ and # operators in Surname and Phone number here.

              • 4. Re: Finding records and printing them


                     Sorry I posted the wrong script. That one was the one I was playing with to try and search / find clients in the first problem I had. I solved that by using one of the script examples. The script shown below is the one I'm trying to use to solve the second problem:

                     NB:- The Perform Find [Restore] reads : Find Records Journey::Print Status: [Yes].

                The above works bar the bit to print the records marked Yes.

                I'm not clear where the part you quote:

                "Set Field [Journey::JourneyID ; Globals::gFilrstrecord & "..." & gLastrecord ]

                Set Field [Journey::PrintStatus ; "Yes"]"

                would go, or if I need to create another Globals Table with First and Last Record fields or whether I need to add it to the Journeys table. 

                At times like this I wish there was a novices guide to scripting finds!!

                • 5. Re: Finding records and printing them

                       Please review the link that I posted. Your second set of script steps also do not match the examples. To repeat.

                       There are no steps in those examples that look like this:

                       Enter FInd Mode [Restore]

                       there are no steps in those examples that look like this:

                       Perform Find Mode [Restore]

                       In every case, the brackets are empty.

                       In each case, the scripted finds follow this general pattern:

                       Enter Find mode [] ---> EMPTY BRACKETS, no pause, no stored find criteria
                       Set Field [ ---> one or more set field steps are used to set up the find criteria.
                       Set Error Capture[on] ----> This keeps an error dialog from interrupting the script if no records are found
                       Perform find [] ---EMPTY BRACKETS here! NO stored find criteria.

                  • 6. Re: Finding records and printing them

                         OK. I think I've got it. I now have the following although the script is not prompting me to specify a start record,(serial ID) or end record and thats because I know I haven't set the Globals (GlobalPrint) field up properly. This is because I don't understand where the the Globals part comes into effect as you said in your earlier post :

                    "All of those scirpts avoid using any stored find criteria. Both Perform Find and enter find mode are listed in those examples with empty brackets. One of the reasons I don't do that is that calculations cannot be evaluated inside a stored request, but can be evaluated in a set field step"

                         Isn't the Globals field a stored field? If so how does this work using your analogy? If this is a Global Field, do I now need to setup a new table called Globals Print with the two fields, gFirstrecord gLastrecord, as global or text? 

                         As I said, scripting is all new to me so my apologies if my questions seem a bit dumb so thanks for your paitence.

                    • 7. Re: Finding records and printing them

                           I'm not talking about stored fields. I'm referring to Stored requests, what you get when you see [Restore] in the Enter FInd Mode and Perform Find steps if you store criteria inside those script steps.

                           Enter Find mode manually and take a look at your screen. All your fields are now empty unless it's a field with global storage specified. Data in global fields is not specific to any one record so you can't enter criteria into a global field while in find mode and perform a find, but you can put data in a global field, enter find mode and then copy the data from the global field to the field where you need to specify your find criteria.

                           The scripted method that I am recommending transfers data from one or more global fields to non global fields with set field steps after entering find mode. In the process, calculations are often performed to produce correctly formatted criteria for you find--saving your users the potentiall confusing complications of having to do this for themselves.

                           Thus, it comes down to the set field steps:

                           Enter Find Mode []
                           Set Field [YourTable::YourCriteriaField ; SomeExpressionGoesHere ]

                           The field in blue cannot be a global field. Any fields referenced in the calculation that you enter where I have red text, however, must be fields with global storage specified or they will be empty at the time the set field step is performed because it is performed while your window is in Fimd Mode.

                      • 8. Re: Finding records and printing them

                             Thanks for the nice clear explanation, it makes sense. So if I understand your explanation properly I setup the SetField to look at my table, Journey and the Criteria Field which would be the JourneyID. The expression should point to a Global Table that contains one or more global fields. Now in my case that will be a new Global Table and I'll call that GlobalPrint for arguments sake with two new fields, gFirstrecord gLastrecord,  set as Global. I would then setup the Calculated Result field with the following: GlobalPrint::gFirstrecord & "…"  & GlobalPrint::gLastrecord. In theory this should then ask me what the first record is and the the end record is so I can enter the desired serial ID (record number) before continuing with the script. Is this correct?

                        • 9. Re: Finding records and printing them

                               You have the correct idea. But a little clarification on Global fields may be of use to you:

                               Global fields can be defined in any table in your file and the values they contain will be accessible from any record, layout, script or calculation in your file. I prefer to put most of my global fields in a Globals table as a way to better manage them in my system, but this is a preference, not a requirement. As a general rule, I only put global fields in a table other than my "Globals" table if I need to use it as a match field in a relationship.

                          • 10. Re: Finding records and printing them

                                 Right, the Globals table makes pefect sense and one idea that I will bear in mind for my next DB. Looking at my DB however, I'm now unsure of what the global field should be as I can't make the journeyID a global field, which I'm guessing it needs to be in order to give me the first and last record, so what do you suggest? 

                            • 11. Re: Finding records and printing them

                                   I have setup the Globals Table with the two Globals Fields but this is still requesting the first and end records as expectedso....what am I doing wrong?expecting it to do.

                              • 12. Re: Finding records and printing them

                                     Yes, JourneyID cannot be a global field. Thats one of those "blue" fields that cannot be global.

                                     I'm not sure that I understand your last post. Global fields are used so that the user can specify criteria just before performing the find script. If the user does not specify the range of values, how else can this range be determined?

                                • 13. Re: Finding records and printing them

                                       OK, if it can't use the journey field what field do I setup as a Global use so the user can interact and choose what records they want? And how do I create this script? I just need a script (and I think I'm almost there with it) except for defining which global fields to use. At the end of the day all I'm looking for is a script that prompts the user to put in the date / record range required rather than been hardcoded.

                                  • 14. Re: Finding records and printing them

                                         You have two sets of fields, one set where the user enters search criteria before performing the find. These should be global. The other set are the current fields in your table where your script uses set field steps to enter criteria from the globals. Those cannot be global. So you can use JourneyID, but not as a global field where the user enters criteria prior ot performing the script.

                                         None of this would be "hard coded". You'd place your global fields on a layoug next  to a button that performs the script. The user enters criteria into the global fields and clicks the button to perform the script.

                                    1 2 Previous Next