6 Replies Latest reply on Apr 9, 2012 9:08 AM by philmodjunk

    Updating a related payments table

    mscogna

      Title

      Updating a related payments table

      Post

      My question has two parts.  The first part being the most important.

      1.)  I have a Members table with payment amount and payment due date.

      When I process monthly billing, I want to write each of the found records to a payments table.

      ie.  Found all records with billing date of 05/01/2012.  Create a new entry in Payments Table for

      each member with the fields I want to store, ie.  Payment Amount, Date Paid, etc.

      The Members Table is related to the Payments Table by MemberId ( I assume ).

      Hope I explained that okay.

      For the Bonus Prize:

      2.)  In the screen for each member, I would like to have a layout I can go to via a button which shows

      all the payment records for the member I am currently viewing.

      Thanks in advance.

        • 1. Re: Updating a related payments table
          philmodjunk

          1)

          Write your script like this:

          Go to Record/Request/Page [first]
          Loop
             Set variable [$Date ; value: Members::Date ]
             Set variable [$ID ; value: Members::MembersID ]
             Set variable [$Amt ; value: Members::PaymentAmount ]
             Go to Layout [Payments]
             New Record/Request
             Set Field [Payments::MemberID ; $ID]
             Set Field [Payments::Date ; $Date ]
             Set Field [Payments::Paymentamount ; $Amt]
             Go to Layout [members]
             Go to Record/Request/Page [next ; exit after last]
          End Loop

          2)
          Go To Related Record [Show only related records; From table: Payments; Using layout: "Payments" (Payments)]

          • 2. Re: Updating a related payments table
            mscogna

            Thanks  PhilModJunk  I will give that a try.  I was also thinking I could just have a portal for #2.  I use a portal to list all

            members connected to the Primary Member, ie a family membership.

            • 3. Re: Updating a related payments table
              mscogna

              Worked like a charm!!!!  Fantastic!  I will be able to adapt this to some other things I want to do!

              Thank You Very Much PhilModJunk

              • 4. Re: Updating a related payments table
                mscogna

                the billing date is always on the first of the month

                So, an added feature to this would be the following:

                popup a window

                type in billing date, ie 05/01/2012

                perform find on billing date input and status field = OK

                the rest of my script works fine if i perform my find manually leaving off my attempted beginning lines

                I have a field Process Date, assigned as a global

                Billing Date is of type Date

                Status is of type text

                I attached a jpeg of my script

                Thanks

                 

                 

                • 5. Re: Updating a related payments table
                  mscogna

                  OK, Disregard.  I think I have got it, with thanks from other stuff posted elsewhere in this forum.

                  I have attached a picture of my solution.  If you see room for improvement, feel free to comment.

                  Thanks.

                   

                  • 6. Re: Updating a related payments table
                    philmodjunk

                    I haven't read every line of that, focusing on the part that sets up the find request. It should work, provided that you specify global storage for Primary members::Process Date. It will not work if the field does not have global storage specified.