8 Replies Latest reply on Oct 26, 2014 2:19 PM by AlejandraGarcia

    Programing date field showing when Payment should be done and that can change depending on date

    AlejandraGarcia

      Title

      Programing date field showing when Payment should be done and that can change depending on date

      Post

      Hi, I would like to calculate a field that shows when next payment (each week, month, etc) should be made...

      I have a "First Payment Field" which is entered manually and I would like to have a "Next payment" field that can be updated automatically depending on current day.

       

      WEEK CASE

      First payment: Oct 10, 2014

      Next payment:  Oct 17, 2014 (First Payment +7)

      When current day = Oct 17, 2014 I would like Next payment to change to Oct 24, 2014 (Next Payment +7) and then When current date is 24th, Next payment should be 31st, and so on.

      Thank you

        • 1. Re: Programing date field showing when Payment should be done and that can change depending on date
          philmodjunk

          If you define Next payment with this calculation:

          First Payment + 7

          and select a date result type,

          It will compute a date 7 days into the future from the date in First Payment. This requires that First Payment be a date field and not a text field.

          Dates in date fields can be thought of as number fields that store the number of days from 12/31/0000 to the date shown in the field so you can compute new dates by adding or subtracting a number of days to/from that date.

          • 2. Re: Programing date field showing when Payment should be done and that can change depending on date
            AlejandraGarcia

            That is the first step, but how can I make that each week, the date changes?

             

                                         First payment           Next payment             Next payment            Next payment            Next payment      

            Payment day        Oct 7, 2014              Oct 14, 2014               Oct 21, 2014              Oct 28, 2014              Nov 4, 2014

            Current date         Oct 7, 2014              Oct 7, 2014                 Oct 14, 2014              Oct 21, 2014              Oct 28, 2014

            Current day would be the date in which Next payment should change automatically

             

            Thanks

            • 3. Re: Programing date field showing when Payment should be done and that can change depending on date
              philmodjunk

              Are these fields or records?

              In either case, you simply keep adding 7 to the previous date, but whether these are fields in the same record or a set of records will require different implementation details.

              • 4. Re: Programing date field showing when Payment should be done and that can change depending on date
                AlejandraGarcia

                I'm having problems because all are fields and the formulas will be related with each other, so I'm struggling with that ...

                I have these fields: Current date, First payment agreed, wich is a not calculated field and from which next payement (=First payment + 7) is calculated, and last payment which would be a calculation field ( =First payment), but last payment date should be changing each week.

                In this table I'm trying to show you what should happen as days pass (Current date) and which fields should be automatically updated (Calculated)

                                                                                                                                                                                                                                                                                                                                                                                                                                                   
                Current datebefore 7 oct8-14 oct15-Oct22-Oct
                First payment agreed7-Oct (manually entered)   
                Last payment 7-Oct (calculated)14-Oct (Calc)21-Oct (Calc)
                Next payment7-Oct (Calculated)14-Oct (calculated)21-Oct (Calc)28-Oct (calc)
                • 5. Re: Programing date field showing when Payment should be done and that can change depending on date
                  philmodjunk

                  Yes, but your table can be created from a set of records or all be a set of fields in one record. The first option is a bit more work to set up, but can be a more flexible approach in the long run.

                  In either case, each calculated date is simply a case of adding different multiples of 7 days to the original date. Those dates can be calculation fields with a date result type, date fields with an auto-enter calculation or a script can generate a set of records and put a different calculated date into each new record. In all three cases, the calculation expression is the same but put into different parts of the system in order to produce the desired dates.

                  • 6. Re: Programing date field showing when Payment should be done and that can change depending on date
                    AlejandraGarcia

                    Everything is from the same record

                     

                    I'm trying this but is not working:

                                                                                                                                                                                                                                                                                                                       
                    CaseCurrent date ≤ First payment day ; Current date > Last payment date and Current date < Last payment day + 7
                       
                    Result1Last paymante date = First paymentLast Payment date = Next payment date
                    Result2Next payment date = First paymentNext payment date = Last payment day + 7
                    • 7. Re: Programing date field showing when Payment should be done and that can change depending on date
                      philmodjunk

                      Can you copy and paste the exact calculation that you are attempting to use that isn't working.

                      I can't quite make sense of your boolean (logic) expressions here as they seem inconsistent in places.

                      • 8. Re: Programing date field showing when Payment should be done and that can change depending on date
                        AlejandraGarcia

                        Yes :)

                        Current date: Datos generales::Fecha | Actual (calculated value)

                        First payment: Datos generales::Fecha | Primer pago acuerdo (entered manually)

                        Next payment: ; Fecha | siguiente pago programado

                        Last payment: Fecha | último pago programado

                         

                        "FECHA | SIGUIENTE PAGO PROGRAMADO" FIELD'S CALCULATION

                        Case ( 
                        Datos generales::Fecha | Actual ≤ Datos generales::Fecha | Primer pago acuerdo ; Fecha | siguiente pago programado = Datos generales::Fecha | Primer pago acuerdo ;

                        Datos generales::Fecha | Actual >  Fecha | último pago programado and Datos generales::Fecha | Actual < Fecha | último pago programado + 7 ; 
                        Fecha | siguiente pago programado = Fecha | último pago programado  + 7
                        )

                         

                        "FECHA | ÚLTIMO PAGO PROGRAMADO" FIELD'S CALCULATION

                        Case ( 
                        Datos generales::Fecha | Actual ≤ Datos generales::Fecha | Primer pago acuerdo ; 
                        Fecha | último pago programado = Datos generales::Fecha | Primer pago acuerdo;

                        Datos generales::Fecha | Actual >  Fecha | último pago programado and Datos generales::Fecha | Actual < Fecha | último pago programado + 7 ; 
                         Fecha | último pago programado = Fecha | siguiente pago programado

                        )