1 2 Previous Next 16 Replies Latest reply on Mar 1, 2016 6:13 AM by Ben_B94

    Problem Script

    Ben_B94

      Hello,

       

      Having trouble with a script provided for me. The generate email script in the file should send an email to the person with their items but it sends items 1 at a time so sending more than 1 email to the same person. It seems to be from the $itemList if statement. The if statement is supposed to set  $itemList to the first item, then go to the next record and re-test which ends in result 2 which is $itemList(the first item) + the current item, from my limited understanding. But for some reason it seems to exit the loop early? And not go to next record or that's what it seems like.

       

      The demo file that was sent to me works fine but I have sat and went line by line checked everything I think? But Please see attached clone file. Thanks.

       

      Regards,

      Ben

        • 1. Re: Problem Script
          siplus

          You should restructure the whole enchilada first. You need a table with people, etc.

           

          The script was a mess so I rewrote it from scratch.

          • 2. Re: Problem Script
            Ben_B94

            Thank you for your reply. Your script does the job perfectly. I now see that I just have 1 giant table that could be broken down. Do I just create the tables then import from my existing one? 

            • 3. Re: Problem Script
              siplus

              Ben_B94 wrote:

              I now see that I just have 1 giant table that could be broken down. Do I just create the tables then import from my existing one? 

               

               

              Well yes and no.

               

              You should at least have a "People" table (ID, Name, mail etc) and a "Assignments" table (Assignment ID, date, ForeignKeyInventory, ForeignKeyPeople).  You can import people data into people, but then you will have to delete the duplicates.

              • 4. Re: Problem Script
                Ben_B94

                Is it possible to edit the script to contain more than one field as the identifier in the email so say the ID num, the model etc? I tried using a calculation field but didnt work. 

                • 5. Re: Problem Script
                  siplus

                  Of course, just change line 7, setting $CurrentAssignedItems from

                   

                  ExecuteSQL("SELECT Item FROM ASSETS WHERE \"Assigned to\" = ?";"";"";$currentName)

                   

                  to

                   

                  ExecuteSQL("SELECT \"ASSET ID MATCH FIELD\", Item, Model FROM ASSETS WHERE \"Assigned to\" = ?"; CHAR(9) ;""; $currentName)

                  • 6. Re: Problem Script
                    Ben_B94

                    Apoligies as I am not being specific enough, I'd like it to list item model category and serial number for each tool. If i put all in it returns ? do i need to change something else, the char(9) im not 100% on how that part works.

                     

                    Thanks

                    • 7. Re: Problem Script
                      siplus

                      The char(9) is the tabulator char, so that the list you get will have its row elements be separated by tabs.

                       

                      The field names of the fields you want must be inserted between SELECT and FROM.

                       

                      They have to be separated by commas.

                       

                      Should a field name contain spaces, its entire name must be enclosed in \"  \", like I did for the ASSET ID MATCH FIELD field.

                       

                      If you get a ? you made some error.

                      • 8. Re: Problem Script
                        Ben_B94

                        Ok thanks, ill give it a go

                        • 9. Re: Problem Script
                          siplus

                          also, if any of the fields you specify have a name included in the list you find here:

                           

                          Reserved words in FileMaker Pro | FileMaker

                           

                          you must use the \"  \" too.

                          • 10. Re: Problem Script

                            Check out the database design class on Lynda.com FOR FILEMAKER:

                             

                            http://www.lynda.com/FileMaker-Pro-10-tutorials/Relational-Database-Design-with-FileMaker-Pro/83839-2.html

                             

                            Excellent stuff.

                             

                            FileMaker encourages you to "jump right in", but that's usually a mistake without some careful planning of your "foundation" (database).

                             

                            If your database isn't well constructed, you'll be spinning your wheels trying to get usually easy or trivial things to work when you're using a well-defined database.


                            Normalization is splitting up a single huge table into related tables. This separation avoids redundancy and other problems.

                             

                            ---

                             

                            There are other resources as well. Invest some time in learning this stuff. Database design is a generic skill that is EXTREMELY valuable whether you're doing FileMaker or some other relational database system

                             

                            Understanding database design is a "step zero" kind of skill.

                             

                            (not that we all aren't trying to get better at it all the time.)

                             

                            Good luck.

                             

                            -- m

                            • 11. Re: Problem Script
                              Ben_B94

                              Hi Siplus,

                               

                              This is out of the blue so apologies. I have now split my table to into several tables.

                              The SQL script runs but returns ben  b, your allocated items are, ?

                              I have removed the \"   \" where necessary and replaced table and field names, what am I missing?

                               

                              Thanks in advance.

                              Ben

                              • 12. Re: Problem Script
                                siplus

                                Line 10

                                 

                                FROM Tools WHERE Staff::Name

                                 

                                replace with

                                 

                                WHERE Name

                                • 13. Re: Problem Script
                                  Ben_B94

                                  Ok thanks, seems the problem is now the ID field used in lines 10 & 11?,

                                   

                                  10. SELECT whatID(tools i guess?), DIX_Num, Model, Category, Serial_Number FROM Tools WHERE Name.......

                                  11. Set field ( whatID?)

                                   

                                  In the old version it was just the 1 big table which had the 1 ID, 10. select pk_id.... 11. set field (pk_id)

                                  Tried various ID's but still returns ?

                                  • 14. Re: Problem Script
                                    siplus

                                    I do see a go to original layout on line 20, but I don't see a go to layout (signOut) which is necessary for the line 11 Set Field to work. Add such a script line, preferably outside the loop , let's say after line 5.

                                    1 2 Previous Next