9 Replies Latest reply on Apr 18, 2011 11:27 AM by AaronSmith

    Please help with script to email a copy of the file with 1 single record

    AaronSmith

      Title

      Please help with script to email a copy of the file with 1 single record

      Post

      I have been working with filemaker for several years, but just now starting to tinker with editing my company database.  I have no training.  I have several outside companies that sell our product for us.  I do not want to give them access to our server.  Currently they e-mail us a "sales order" printed from filemaker into a PDF file.  We then print review and manually type that same order into our filemaker sales order file.  I have been trying to create a button that will email the record they create in the sales order file.  Then in theory we could receive the email, open it, review it, and then click another button to load the order into our sales order file.  Both our sales order file, and their sales order file are exactly the same. Their file only contains the records they send us.  Every time I attempt this I run into a problem.  Can someone tell me how to build both scripts?  One to send the record, and one to load it into our file after review?  We just upgraded to filemaker 11, and all of the offices will be getting Filemaker 11 once these issues are solved.  Thank you in advance for your efforts.

      Aaron 

        • 1. Re: Please help with script to email a copy of the file with 1 single record
          AaronSmith

          Is there anyone out there who has tried this?

          • 2. Re: Please help with script to email a copy of the file with 1 single record
            philmodjunk

            With FileMaker 10 or 11, the process is the same. Your outside companies should be able to run a scrip that exports the current found set (which can be a single record, if necessary) to a FileMaker file. This exported file can be emailed to you by the same script where someone on your end can then use a script to find and import the data into your file.

            The details can vary a bit depending on the structure of your files to make sure that auto-entered serial numbers, etc. work for both you and the outside company, but it can be done and it can be fully automated, except for the import step on your end, where you need someone to download the file attachment.

            "Every time I attempt this I run into a problem."

            What have you attempted and what problems have you run into?

            • 3. Re: Please help with script to email a copy of the file with 1 single record
              AaronSmith

              Phil,

              Thank you very much for your response.  I see what you mean about the auto enter numbers.  My solution is to import their number into a separate field, and have our order file use its own auto-enter creation number.  I have trouble even getting started.  I first started with the export option for the script.  This only saves the field information not the layouts and other information.  I also tried snapshot link, but I think that is for 2 people using the same file with all the same records.  So I tried to walk through what I would do manually to accomplish this.    

              1. Copy the "sales creation number"

              2. Enter find mode click paste, and hit enter 

              3. Save a copy of the file, and open the new file

              4. Delete all records in the copy

              5. Import from the original file the current look-up

              6. Email the file

              I can accomplish step 1, but cannot figure out step 2 and paste into the find mode.  

              I did try other things, but nothing worked.  

              I was hoping you might be able to tell me how you would do it, because I am obviously not doing anything the right way.  Thank you again for the response.

              • 4. Re: Please help with script to email a copy of the file with 1 single record
                philmodjunk

                "This only saves the field information not the layouts and other information."

                That is correct, but why does it matter? The signficant data should all be recorded in the fields of the table or tables. Your outside companies can send you clones of their files if you need to see the layouts, but you should be able to design your system so that the layouts are not needed, just the data in one or more tables.

                What "other information" are you talking about here?

                "I also tried snapshot link, but I think that is for 2 people using the same file with all the same records."

                Also correct and thus not an option for you here.

                Your steps don't really make sense to me. What do you mean by "copy the sales creation number"? Why do you need to do this? Shouldn't your outside companies have a script that exports all new records into a single file once a day? Save a Copy can be made to do this if you aren't hosting the file from FileMaker Server, but doesn't seem to be needed here.

                If you are finding a record in step 2, why do you then delete the records in the copy after you save it? Wouldn't you just find and delete the other records in this copy? (And this is why I wouldn't use save a copy here.) Or save a clone (empty copy) and then import?

                • 5. Re: Please help with script to email a copy of the file with 1 single record
                  AaronSmith

                  That is correct, but why does it matter? ---  We need the ability to review the  information, and approve it before we enter it into our file.   Sometimes  prices, shipping locations, etc. is not acceptable.

                  What "other information" are you talking about here? --- I am referring to the  button I want to create to export the record into our file.

                  "Your steps don't really make sense to me." --- This is how i learned to do it.  It works for me, but it sounds like you know a much better way to accomplish  this.  

                  "Why do you need to do this? Shouldn't your outside companies have a script  that exports all new records into a single file once a day?" --- Again we want to  review each order sent.  Also, once per day is not enough.  Some orders need  immediate attention.  There is also a benefit to the email.  It tracks when each order was sent, and what original information was sent.  If there is ever a discrepancy, then we know where the error was made.

                  If you are finding a record in step 2, why do you then delete the records in  the copy after you save it? Wouldn't you just find and delete the other records  in this copy? (And this is why I wouldn't use save a copy here.) Or save a clone  (empty copy) and then import? --- I think I understand what you are saying.  Each  office needs to have access to the orders they put in.  If those records are  deleted they would not have a file with all of their orders to us in it. 

                  Thank you again.

                  • 6. Re: Please help with script to email a copy of the file with 1 single record
                    philmodjunk

                    That is correct, but why does it matter? ---  We need the ability to review the information, and approve it before we enter it into our file.   Sometimes prices, shipping locations, etc. is not acceptable.

                    Make it a two stage import. Stage one pulls it into your system for review. Stage two changes a status field in the imported data to show that it is approved. If you want, the unapproved records can be deleted after you review them. (In the rest of your system, there are several ways to keep "unapproved" records from being accessible to the average user.) The exported/imported records can be single records or groups of records. Once imported the review/approval process would be the same. The data can be exported from FileMaker to a text file, attached to an email and sent to you for import into your system for review.

                    If you don't want to import the data directly into your current table, you could also define a separate table where you can review the record before a script imports the approved record(s) into your main table, but this really shouldn't be necessary as a long term solution here, if you can set up proper controls to limit accessibility to newly imported, but not yet approved records.

                    The "tracking info" can be exported as part of the data you then import to save the need to refer to past emails or to physically copy data from the email into your database. A related table, can thus record when the data was sent to you, from whom, why it was sent etc...

                    Seems to me that the simplest solution is to establish accounts/passwords/privilegesets so that your outside companies have direct, but limited access to your database so that you do not have to import any data at all. They could then enter this data directly into your database, but the new records would be sequestered from access by users at your location until they are reviewed and approved.

                    • 7. Re: Please help with script to email a copy of the file with 1 single record
                      AaronSmith

                      Ok.  It sounds like what I want to do isn't actually possible.  I agree your solution is probably the best.  It also requires me to hire in a company to set that up.  Unfortunately, that is not an option for us today.   Thank you again for all of your help. 

                      • 8. Re: Please help with script to email a copy of the file with 1 single record
                        philmodjunk

                        It is possible, just not necessary to do so. I'm suggesting a better alternative that reduces the complications possible in what you originally described.

                        Your external companies, can certainly use the "clone" option to save a copy of their database. They can then import the selected record from their main file into this empty copy and email it to you. It's just a lot of extra steps for including mutliple layouts and scripts---slowing down the upload and downloading of attachments when all you really need here is the data in the record.

                        • 9. Re: Please help with script to email a copy of the file with 1 single record
                          AaronSmith

                          I see what you mean.  I will try the clone option, and see where that takes me.