11 Replies Latest reply on Oct 2, 2009 11:26 AM by seaserpent

    Exporting one record from found set

    seaserpent

      Title

      Exporting one record from found set

      Post

      I've written a script to find a record based on ID# then export that record to a file. The problem is that the export always export all the records in the db. I thought that export only exported the found set. What am I doing wrong?

       

      Here's my script steps:

       

      New Window []

      Show All Records

      Enter Find Mode [Pause] - user enters ID#

      Set Field [my field here]

      Perform Find []

      Omit Record

      Show Omitted Only

      Export Records [No dialog; "Export_to_Ship.fp7"; Unicode (UTF-16)]

      Show All Records

      Close Window [Current Window]

       

      Even when I find a record and simply use the export command, it still exports all records.

      Thanks for your help.

       

      Filemaker Pro 9 Advanced

      Mac OS 10.5.8

      Intermediate experience 

        • 1. Re: Exporting one record from found set
          Orlando
            

          Hi seaserpent,

           

          I think the problem is the omit step after you find:

           

          ... 
          Perform Find []
          Omit Record
          Show Omitted Only
          ... 

           

          Are you trying to isolate a single record here? or should the omit be inside the search request?

           

          If you can explain exactly what you are trying to find, and omit, that will help.


          • 2. Re: Exporting one record from found set
            seaserpent
              

            I am trying to export one record at a time, have it saved to a file, then import that single record into a shipping program.

             

            User activates the script and it opens new window in find mode with id# field selected. User enters Id# and clicks enter. Script resumes and saves record to file. User then activates a shipping database where I have a script to import the exported file. (I'm going to combine these steps later into one).

             

            So, my script isn't exporting just the one record as desired. It exports all records.

             

            Thanks for replying. 

            • 3. Re: Exporting one record from found set
              Orlando
                

              Is the user in that record before running the process? How do they get the ID? 

               

              So are you searching for 1 record, and exporting just that one record? Is the ID unique in that table, will it only ever return 1 record? If not you would need to put in some checks.

              • 4. Re: Exporting one record from found set
                seaserpent
                  

                The user is in the record and exporting just that one record. The ID is unique to that table and will only ever return 1 record.

                 

                Isn't it correct that without using a script - You find a record, it is the only shown record. If you Export... that should be the only record that is exported? If so, that isn't working in my db. I created an experimental db called contacts to test the export. It works as planned - exporting only the one record. So, the problem then is with what table I am actually in, correct?

                 

                Looking at my user table of origination - it is the Sales Order table. Every field is in that table but the data is from another table "Contacts". Could that be the problem? Here is a screen shot of the fields in layout mode. The exported fields are in the Ship To section:

                 

                sales orders fields 

                • 5. Re: Exporting one record from found set
                  mrvodka
                    

                  I cant see your last attached script, but there is an issue with your orig find.

                   

                   

                  Enter Find Mode [Pause] - user enters ID#

                  Set Field [my field here]

                  Perform Find []

                  Omit Record

                  Show Omitted Only

                   

                   

                  Lets say you have records number 1 - 10. User enters 7. It returns one record with ID 7. Then you Omit it. Now you have no records in your found set. Then you show Omitted only. That means you just asked to show the opposite of your zero found set, which in turn is all 10.

                   

                   

                  • 6. Re: Exporting one record from found set
                    seaserpent
                      

                    Thanks mr_vodka.

                     

                    Here is script without the omit. I've tried that scenario also and it still exports more than 1 record, but only half as many as before. That tells me something is amiss.

                     

                     

                    New Window []

                    Show All Records

                    Enter Find Mode [Pause] - user enters ID#

                    Set Field [my field here]

                    Perform Find []

                    Export Records [No dialog; "Export_to_Ship.fp7"; Unicode (UTF-16)]

                    Show All Records

                    Close Window [Current Window]

                     

                    • 7. Re: Exporting one record from found set
                      mrvodka
                        

                      First you dont need a Show All step. Second, what is the Set Field for? Third, you mention you had a related record on the layout. Does the parent record have multiple child records? If so, try grouping the parent ID. ( upper right hand corner)

                      • 8. Re: Exporting one record from found set
                        Orlando
                          

                        What is happening in the "Set Field [my field here]" script step?

                         

                        New Window []

                        Enter Find Mode [Pause] - user enters ID#

                        Perform Find []

                        If [ Get ( FoundCount ) = 1 ] 

                        Export Records [No dialog; "Export_to_Ship.fp7"; Unicode (UTF-16)] 
                        Else
                        Show Message [There was an error finding this record, please try again.] 

                        End If

                        Close Window [Current Window]

                         

                        And see if this works. 

                         

                         

                        • 9. Re: Exporting one record from found set
                          seaserpent
                            

                          Great tips there Orlando. Will implement that when problem is resolved.

                           

                          What is happening in the "Set Field [my field here] " script step is a pause in the script so user can enter the id# of the record to find for the export.

                           

                          Tried the suggestions and still no go. But, I did discover this: What is getting exported is the related table "contacts". All records in that table are being exported because the total records always matches the total records in that table. The one record that I want to export is always the first record to show in the export and it has all the data intact. All remaining records in the export have info filled in two of the fields only. So I need to find out how those others are being referenced and I think my problem will be solved.

                           

                          I'll report back. 

                          • 10. Re: Exporting one record from found set
                            Orlando
                              

                            Which method are you using to export the records?

                             

                            The Export Records script step or Save Records as Excel

                             

                            The Export Records step allows you to control the fields you export, if you are using this double check you have not selected any related fields in the Fields export order.

                             

                            If your using the Save Record as Excel then you will need to make sure there are no related records on the layout you run this script step on, as it exports all the data for the fields on the active layout instead of allowing you to pick and choose.

                             

                            You should only get one record if you remove the related records from the export. 

                            • 11. Re: Exporting one record from found set
                              seaserpent
                                

                              Thanks to both of you for the replies. The problem was two of the fields specified in the export were from a different table, but with the same names. I feel like an idiot, but extremely glad to have this solved.

                               

                              I will implement your suggestions and make it even better. This forum is great - like Filemaker.