1 2 Previous Next 18 Replies Latest reply on Mar 10, 2017 5:08 PM by stephaga

    Should have been simple... but I'm stuck!


      I'm an organic grower! Built a database to manage my garden, and each year, I add bits and pieces to it! Each year I get stuck!!! I'm really hoping to find help here!


      Here's what I need to do:


      I have a table that contains VEGETABLES and a related table that contains SPECIFIC TASKS that have to be done from the moment I sow that vegetable (fertilize, prune, transplant, so on!)


      I also have a table named VARIETIES (for each vegetable, I sow several varieties.. example for tomatoes, I would have "italian" "cherry" etc etc


      Each time I sow a variety, I create a BATCH with the date, the number of seeds that I've been sowing, and so on ( I make several batches of the same variety, most of the time!)


      When I create a new BATCH, I would like to create automatically a list of RELATED TASKS with dates that would be calculated from the date that batch has been sown...

      I don't know if I'm being clear... I made a graph to isolate the concerned tables...


      I've been trying quite a few things with no success and I'm desperate to get this to work before the growing season starts!


      I hope you can give me a hand with that!! Thanks so much!



        • 1. Re: Should have been simple... but I'm stuck!

          You've made a good start. But it's not clear where precisely that you are getting "stuck" in making this work.


          I do see one addition to make to your graph:




          Batches::__pkBatchID = RelatedTasks::_fkBatchID

          SpecificTasks|ByTask::__pkTaskID = RelatedTasks::_fkTaskID


          This is a second occurrence of specific tasks. The first links to Vegetables by Vegetable ID but this one links by Task ID to your related tasks.


          You can think of a "batch" as similar to an "invoice" with RelatedTasks as the "line items" listed on the invoice from a "catalog" of specific tasks.


          A script can take a Batch's vegetable ID to get a list of Task IDs from Specific Tasks in order to create the needed set of records in RelatedTasks.

          1 of 1 people found this helpful
          • 2. Re: Should have been simple... but I'm stuck!

            Thank you so much, philmodjunk, for this reply!

            You are confirming that I was on the right track! I did create a table occurence for "Specific Tasks" and linked it to "Related Tasks" (I'm attaching a copy of my graph), I didn't mention it first, cause I was unsure this was right!


            and I quickly realized that there was an other step to make, because my tasks would appear (in a portal that I made in my Batches layout) but with all the same dates, or only one would appear out of 5... it just wouldn't work...


            so I was on my way to create a script to grab infos from Specific Tasks and brings them into Related Tasks each time I create a new record in Batches... but you know what? Not only I wasn't sure it was the right thing to do, but I don't know how to do that!!! I haven't been using scripts very much... only really simple ones..


            SO... would you be kind enough to give me a hint on what kind of script I should use??


            Many thanks in advance!!!




            • 3. Re: Should have been simple... but I'm stuck!

              From a layout based on Lots Serre (Batches), You can run a script to create the set of related tasks records  for the current Batch record as follows. This is not how I would actually do it, but it fits your current data model and illustrates the needed concepts. If you can get this to work, you can consider the method that I would use--which requires additional Table occurrences:


              Set Variable [$BatchID ; Lots Serre::__pk_IDLots Serre ]

              Set Variable [$TaskIDs ; List ( SERRE Taches entre...::__pkIDSERREENtretien ) ] -- can't see full table occurrence name here

              Go To Layout ["Related Tasks" (SERRE_Taches prev...) ] -- this step can trip script triggers


                 Set Variable [$K ; Value: $K + 1 ]

                 Exit Loop If [$K > ValueCount ( $TaskIDs ) ]
                 New Record/Request

                 Set Field [ SERRE_Taches prev...::__fk)IDLotsSerre ; $BatchID ]
                 Set Field [SERRE_Taches prev...::_fk_IDTachesSpec ; GetValue ( $TaskIDs ; $K ) ]

                 //code here can set up the dates for each related task by referencing a date from Batches and adding to it a value from the now linked in specific tasks record to get the actual date for that related task.
              End Loop

              Go To Layout [ Original Layout ] -- this step can trip script triggers


              The actual method that I would use here would use a method called MagicKey. It would still get the list of TaskIDS in a variable and still creates the reated tasks by setting the ID fields, but it does so without actually changing layouts. There are excellent articles on how to set up MagicKey that you can web search if you are interested.

              1 of 1 people found this helpful
              • 4. Re: Should have been simple... but I'm stuck!

                Thanks again philmodjunk,


                I've written the script and created a button to activate it in my "batches" layout,

                At first, I had mistakenly used the original table "Related Tasks" in setting the variable $TaskIDs (instead of the table occurence) so when I hit the button, it was generating tasks, but only by duplicating the first task:  if I had set 5 tasks for onions, it would duplicate the first task 5 times.


                I then corrected it and used the table occurence, and now when I hit the button, it stands still.. nothing happens...

                Here is a copy of my script... I am very sorry for the french, here! I think I have the translation right, though!


                Many thanks, again!




                And a video too!






                Capture d’écran 2017-03-09 à 09.28.58.png

                • 5. Re: Should have been simple... but I'm stuck!

                  The script itself does not have any obvious errors. I can't check every detail from the screenshots provided however.


                  The most likely source of a problem would be to perform this script from the wrong layout.

                  The Script, as written, must be performed from a layout that specifies: "Lots Serre" in layout setup | Show Records From.


                  If you have FileMaker Advanced, you can also enable both the script debugger and data viewer before running this script. You can then step through the steps of the script while watching the values change in your fields and variables. That can often make any error in the script very easy to spot.

                  • 6. Re: Should have been simple... but I'm stuck!

                    I've just verified, and I'm performing the script from the right layout (Show Records From Lots Serre) Unfortunately, I'm working with Filemaker Pro 12...  "script debugger" and "data viewer" sound nice!

                    • 7. Re: Should have been simple... but I'm stuck!

                      It's important to let people know when you are using an older version. It didn't happen here, but I could have wasted a great deal of time suggesting a solution that would not work in your version.


                      What you can do is temporarily insert show custom dialog steps in several points inside your script and set them up to display values in different fields and variables. This will then pause the script at key points where you can examine data on the current layout and the data shown in the dialog box.


                      You can also look at the data in your related tasks table just after running this script and see if any new records were added and what data was entered into different fields. If a match field is empty, you know that it wasn't getting data from your script as expected and this might tell you what to examine.


                      One very important variable to check is the $TaskIDs variable. If it's not getting anything from that List function, your script won't work.

                      • 8. Re: Should have been simple... but I'm stuck!


                        Well... No new record is being made in the "Related tasks" table after I run the script

                        I've tried inserting custom dialog boxes and get them to display fields at different places, but the dialog box  remains blank when I run the script.

                        Concerning the variables, I'm not sure how to check them? I've only found ways to choose fields to display in dialog boxes...

                        • 9. Re: Should have been simple... but I'm stuck!

                          put the name of the variable inside the custom dialog to show the value.


                          List ( Table::Field ; $Variable1 ; $Variable2 )


                          for example, would list the value of Field, $Variable1 and Variable2, in a vertical list inside a custom dialog.

                          • 10. Re: Should have been simple... but I'm stuck!

                            I've just tried differently, and noticed something else...


                            If I insert "Show custom dialog" just after "Set Variable [$K ; Value: $K + 1 ]" or higher in the script,  It will show when I run the script...

                            If I put it lower in the script, it won't!

                            • 11. Re: Should have been simple... but I'm stuck!

                              You might also put a portal to the original occurrence of Specific Tasks on your batches layout. It will show you what records would supply values to the list function used in your script. If the portal is empty, you have a problem with either values in a field, a record in a table or the relationship that is breaking the link between your current Batch record and the specific tasks that should be linked to it by Vegetable.

                              • 12. Re: Should have been simple... but I'm stuck!

                                It gives me :"?"

                                • 13. Re: Should have been simple... but I'm stuck!

                                  Sounds like you are not getting a list of Task ID's. If there are no values in that variable, there's no loop.

                                  • 14. Re: Should have been simple... but I'm stuck!

                                    I've tried:

                                    if I make a portal from the Original table, it's fine.

                                    If I choose the table occurence, it's empty

                                    1 2 Previous Next