5 Replies Latest reply on May 28, 2010 11:20 AM by philmodjunk

    automatically adding data to a repeating field or a portal field

    Mick@YGO1

      Title

      automatically adding data to a repeating field or a portal field

      Post

      I am trying to move over a database app we have in filepro at the moment to filemaker 10 (or 11 shortly).  I need to add an amount to a field automatically depending on the current date. IE if  a due date is => todays date add £10.00 to a field and a date to another field. This would need to happen on all records in the database.

      I can do this easily in filepro and excel but i am struggling in filemaker. If scripted it could be linked to a report as we run reports monthly. Any pointers woulkd be appreciated.      Mike

        • 1. Re: automatically adding data to a repeating field or a portal field
          philmodjunk

          There's several different approaches possible and a script is one of them. The "triggering event" is a key detail that's not in your first post. When do you want this to take place?

           

          Each time the file is opened?

          When you enter data in a partcular field?

          Something else?

           

          Another question: Is the £10.00 an amount that is added for every day the due date is exceeded or just the first time today's date matches or exceeds the due date? (I'd assume the latter, but it pays to be sure.)

          • 2. Re: automatically adding data to a repeating field or a portal field
            Mick@YGO1

            Many thanks for quick reply, I have been looking into scripts but became stuck very early on!!

             As an example we have customer who pays monthly for items so if Mr Smith owes us, £10 each month due on the 15th of each month, we need the monthly amount due (£10) to be added to a field along with the due date of 15/05/10 added to a date field.The due date would then increment by 1 month to 15/06/10.  

             

            This would need to run on each record every time we either access (or could run a report which then runs the script.)  The app we have at moment just checks the due date against todays date -- if due date => today then add amount to due field and add the due date to date field - Increment due date by 1 month.

             

            We need to keep about a years worth of  'due dates' - 'dates paid' so i was either using  repitition fields of 12, or using a portal with just the dates and amounts linked to the record in the database. 

             

            Hope this explains the process required. 

             

            Many thanks again

             

            Mike

            • 3. Re: automatically adding data to a repeating field or a portal field
              philmodjunk

              This sounds like something you might implement using a table set up to function like a bookkeeper's "ledger". You would have fields such as the following for this purpose:

               

              PersID (Number, link to customer's ID number in Customer table), TransDate (auto enter current date), DueDate, Debit (Paymentsreceived), Credit (Amounts billed) , cBal (Debit - Credit), sBalance (Summary field, Total of cBal, Running Total)

               

              Would that work for you?

               

              If so, your script might work like this:

               

              Enter Find Mode []

              Set Field [LedgerTable:: DueDate ; "< " & get ( CurrentDate ) ]

              Set Error Capture [on]

              Perform Find[]

              Set Error Capture [off]

              Sort [No dialog ; Restore] //Sort by DueDate and PersID so omit step doesn't leave you on wrong record

              Go To Record [First]

              Loop

                      Exit loop if [ Get ( FoundCount ) = 0 ]

                      Set Variable [$DueDate ; let ( d = Ledger:: DueDate ; Date ( month ( d ) + 1 ; day ( d ) ; Year ( d ) ) ]

                      Set Field [Ledger:: DueDate ; "" ] //clear this field so it won't be found and processed a second time

                      Duplicate Record/Request

                      Set Field [Ledger:: DueDate ; $DueDate ]

                      Go To Record [Previous]

                      Omit Multiple Records [No dialog ; 2 ]

              End Loop

               

              There are a number of variations possible on this approach, but that's the basic idea.

              • 4. Re: automatically adding data to a repeating field or a portal field
                Mick@YGO1

                Yes!!  Thats the sort of thing i am trying to accomplish. I have the fields required very much as you show in the example so all i need to do is sort out the scripting. 

                 

                Are there any recomendations for any Filemaker script  books, i have found a few online but you never quite know how good they are until you buy them. The filemaker script pdf on line seems very basic and doesnt go into much detail.

                 

                Many thanks for your help on this.

                 

                Mike

                • 5. Re: automatically adding data to a repeating field or a portal field
                  philmodjunk

                  I can't really recommend any specific titles. Others may post their recommendations of course, but one thing you could do is go to a site like Amazon, search out the titles they offer and read the reader reviews to get an idea as to whether they're a good option for you.