3 Replies Latest reply on Feb 22, 2012 8:40 AM by philmodjunk

    Need Help with Select from Multiple repeated fields

    AJRobey

      Title

      Need Help with Select from Multiple repeated fields

      Post

      I think this is what they call repeated fields?  Please look at the image.  I want to be able to do many things with individual lines of transactions, but when I export, it comes out strangely.

      I want to be able to:

      1) Select an amount based on a date
      2) Be able to count the dates and exempt something if there is a date less than the newest date
      3) Be able to select the first, or last transaction

      Among other things.  If I can get a grasp of how to manage this, it would be great.  Any help would be appreciated.  And I want to thank those that have helped me in the past before, you have been an asset.  If you have a "tip jar" let me know.

      Thank you! 

      repeatingfields.jpg

        • 1. Re: Need Help with Select from Multiple repeated fields
          philmodjunk

          I think this is what they call repeated fields?

          Fortunately, these are fields from a related table rather than a repeating field--which would not be a good idea here.

          I see that you have a portal to a table occurrence named "payments". A portal requires a relationship between the layout's specified table occurrence and the portal's table occurrence. Layout Setup | Show records from will identify the table occurrence for the layout.

          What is the relationship here?

          From the context of the layout's table occurrence, #3 is easy to answer. A reference to any field in Payments will refer to the first record shown in the portal. The Last() function can be used to refer to the last records listed in the portal--assuming that this is not a filtered portal. If you have a portal filter for Payments, we'll need to rethink that.

          "Select an amount based on a date"

          This is where knowing the relationship used for the portal would help. I'll guess at it for now:

          Client::ClientID = Payments::ClientID

          If you create a new occurrence of Payments, you can link it to Client like this:

          Client::ClientID = PaymentsByDate::ClientID AND
          Client::gSelectedDate = PaymentsByDate::DateClosed

          gSelectedDate would be a date field and often it is best to make it a field with global storage specified. With this relationship, you can select or enter a date in gSlectedDate and then any references to fields from PaymetnsByDate will refer to the first Payment record linked to client to have the same exact date.

          "Be able to count the dates and exempt something if there is a date less than the newest date"

          It's not clear to me what you want here. You can count the related payment records with count ( Paymetns::ClientID ). Finding a related payment record with a date less than a specified date can be done with a modification of the above relationship:

          Client::ClientID = PaymentsByDate::ClientID AND
          Client::gSelectedDate > PaymentsByDate::DateClosed

          But I have no idea what database changes are needed to "exempt something".

           

          • 2. Re: Need Help with Select from Multiple repeated fields
            AJRobey

            This helped a lot, however, I cannot seem to figure out how to count the number of fields with the same date in them.  What I am trying to do is this:

            Traffic Court Main::Date Closed is the main closed date

            Payments::Date Closed is the payment that was made, in which there can be more than 1.

            I've tried:

            Count(Payments::Date Closed = Traffic Court Main::Date Closed) > 1 but it seems that produces an error.  I am trying to prepare an if statement where if the dates closed = each other, count the number of them, and if it is greater than 1, continue

            and when continued, I would want to add the values for all Payments::Amount that have that same date closed, and set them to another field.

            • 3. Re: Need Help with Select from Multiple repeated fields
              philmodjunk

              You seem to be confusing calculations with relationships.

              If you go to manage | Database | Relationships and create this relationship:

              Payments::Date Closed = Traffic Court Main::Date Closed

              Then you can define a calculation field with the Count function to count related records that match via this relationship:

              Count ( Payments::Date Closed ) defined in a calculation field in Traffic Court Main, for example, would count all records in Payments with the same date closed date as the current record in Traffic Court Main.

              This counts ALL records of the same date not all records of the same date for a specific court case or specific client so you may need to add another pair of fields to the above relationship to get the correct count if you need payments made for a specific client or court case.