13 Replies Latest reply on Jan 21, 2010 4:55 PM by philmodjunk

    Reporting using a Value List ID

    PWGKing

      Title

      Reporting using a Value List ID

      Post

      I'm using FMP 10 Adv and my script writing knowledge is zero and the manual does not help. Wish there was a manual for dummies like me.

       

      I wish to generate a report from my database using the value list ID "Machine Workshop".

       

      This report is to only show assets held in the Machine Workshop.

       

      Can someone supply me with a script that will do the job.

       

      kindest regards

       

      PWGKing

        • 1. Re: Reporting using a Value List ID
          philmodjunk
            

          Is this what you want?

           

          Select "Machine Shop" from a value list

          See a report of all records with "Machine Shop" in a specific text field?

           

          Here's a script that will find all records labeled with a value selected from a value list:

           

          Define a global field for your value list, I'll call it "gLocation" in my example.

          Format it with your value list.

           

          Write a script:

          Go To Layout [//select a layout where you want to see your records]

          Enter Find Mode[]

          Set Field[YourTable::YourTextField ; YourTable::gLocation]

          Set Error Capture[on]

          Perform Find[]

           

          Use a script trigger to perform the script. OnObjectExit works for Drop down lists. OnObjectModify works for pop up menus.

          • 2. Re: Reporting using a Value List ID
            PWGKing
              

            Thanks Phil

             

            I'm truely sorry but I am a total dummy when it comes to this part of FMP. Creating the database was fantastic and easy for me because it is similar to ACT which I have used before but script writing is totally foreign to me.

             

            Define a global field for your value list, I'll call it "gLocation" in my example. Format it with your value list.

             

            How do I do this? My database field is called "Site Facility" and the value list contains "Machine Workshop" which I want to capture in my report.

             

            PWGKing

            • 3. Re: Reporting using a Value List ID
              philmodjunk
                

              Select Manage | Database | Fields from the File menu.

              Enter gLocation as the new field, select Text as its type and click Create.

              Click the options button

              Click the storage tab in the field options pop up.

              Select the "global" storage option.

              Click OK repeatedly to get back to your layout.

              Enter find mode.

              Use the field tool to add this new field to your layout.

              Format it with your value list.

               

              Let me know if that gets you over the hump or have more questions.

               

              • 4. Re: Reporting using a Value List ID
                PWGKing
                  

                I have carried out everything as per your instructions and the only thing that happens is that my database duplicates itself. It appears not to be searching for and extracting "Machine Workshop" records only.

                 

                Also how do I do this. Can not find OnObjectExit or OnObjectModify so I tried OnLayoutOpen I think.

                 

                Use a script trigger to perform the script. OnObjectExit works for Drop down lists. OnObjectModify works for pop up menus.

                 

                PWGKing

                • 5. Re: Reporting using a Value List ID
                  philmodjunk
                    

                  I see an error in my last post. It shouldn't "duplicate" anything but my directions should read:

                   

                  Select Manage | Database | Fields from the File menu.

                  Enter gLocation as the new field, select Text as its type and click Create.

                  Click the options button

                  Click the storage tab in the field options pop up.

                  Select the "global" storage option.

                  Click OK repeatedly to get back to your layout.

                  Enter find mode.

                  Enter Layout Mode.

                  Use the field tool to add this new field to your layout.

                  Format it with your value list.

                  Return To Browse Mode.

                   

                  This just sets up the field so the script can use it. You have to select Machine Workshop from the gLocation and then run the script to find all records with Machine Workshop in a text field you specify in your script.

                  • 6. Re: Reporting using a Value List ID
                    PWGKing
                      

                    Once again I have completed everything as you have stated and all my database records are displyed not just Machine Workshop.

                     

                    Scrip I have written is as follows: (Layout is called Machine Shop Plant)

                     

                    Go To Layout ["Machine Shop Plant"]

                    Enter Find Mode[]

                    Set Field[Calibration Register;;Site Facility ; Calibration Register::gLocation]

                    Set Error Capture[on]

                    Perform Find[]

                     

                    PWGKing

                     

                     

                    • 7. Re: Reporting using a Value List ID
                      philmodjunk
                        

                      Hmmm, two things you can try:

                       

                      Remove the set error capture step and see if you get an error message popping up.

                       

                      After running the script, select Modify Last find from the records menu and see what criteria were actually entered into the fields.

                       

                      Those two tests may give us a clue.

                       

                      Also, is Calibration Register::Site Facility a text field?

                      • 8. Re: Reporting using a Value List ID
                        PWGKing
                          

                        Removing the Error Capture step was accepted but a error message came up when running the script (there are no valid criteria in this request).

                         

                        When running Modify Last Find my Database appeared with every field blank.

                         

                        Also Site Facility is Indexed.

                         

                        PWGKing

                        • 9. Re: Reporting using a Value List ID
                          RickWhitelaw
                            

                          Hi,

                           

                          Using "Set Error Capture[on]" gives you no benefit if you don't process the possible errors, i.e. If(GetLastError)=401 (no records found) then "do this . . .". Phil's advice was spot on. If you're not processing the errors it's much better to see them as your script executes since the script pauses until you deal with the ensuing dialog.

                           

                          RW 

                          • 10. Re: Reporting using a Value List ID
                            PWGKing
                              

                            Thanks RW

                             

                            That would be great if I was an expert with regards to script writing etc. But unfortunately I am not and just would like a script to work without having to carry out any diagnostics through the process.

                             

                            PWGKing

                            • 11. Re: Reporting using a Value List ID
                              philmodjunk
                                

                              OK, that error message was revealing.

                               

                              While I agree in principle with Rick, in this case, if the script and layouts are set up correctly, the only possible error result should be "no records found" and a simple test using get (foundcount) can be used if you want the script to tell users that no records were found. (In other cases, it can be obvious to the user because they get a record set of zero records.)

                               

                              Since the error message states: "no valid criteria". There's something not set up correctly with your script and thus it's not setting up valid find criteria.

                               

                              Take a careful look at this line in your script:

                               

                              It should look like: Set Field[Calibration Register::Site Facility ; Calibration Register::gLocation]

                               

                              If it looks like: Set Field[Calibration Register::Site Facility [ Calibration Register::gLocation]]

                               

                              You've got Calibration Register::gLocation in the wrong place.

                              Open the script and double click it.

                              In the repetition box, change Calibration Register::gLocation back to "1"

                              Click OK.

                              Click the lower specify button (calculated result) and enter Calibration Register::gLocation in the specify calculation dialog that pops up.

                              • 12. Re: Reporting using a Value List ID
                                PWGKing
                                  

                                It should look like: Set Field[Calibration Register::Site Facility ; Calibration Register::gLocation]

                                 

                                My script is exactly the same as above but I completed every step as per your instructions again. Unfortunately the same error message appeared again.

                                 

                                I will play with it over the weekend and double check everything again.

                                 

                                The only other thing I can think of at this point is the gLocation field - you instructed to link it to my Site Location field. Should there only be 1 location in there or can it have all the locations as per the dropdown menu that has been developed for my database?

                                 

                                Thanks for your time

                                 

                                PWGKing

                                • 13. Re: Reporting using a Value List ID
                                  philmodjunk
                                    

                                  There's the likely issue there.

                                   

                                  gLocation should be a field with global storage enabled. If you don't set it up this way, the script won't be able to access the contents of the field while in browse mode and you'll get the error message you describe.

                                   

                                  Double click the field's definition in Manage | Database | Fields and click the storage tab. Select the use global storage option for this field.

                                   

                                  PS. Global fields can be defined in any table and they can still be accessed from any layout in your file.

                                   

                                  There is an alternate method that works when you text field is not global also.