2 Replies Latest reply on Aug 1, 2011 7:52 PM by AllanMagsino

    How can I get the last value record



      How can I get the last value record



      I am new to filemaker and I have a question. Here is the scenario....I have a field Subscription Type and Renewal. The Subscription type value list are usually Full Year 2011, 3Months Subscription 2011, Full Year 2012, 3 months Subscription 2012 and so on. 

      If the customer ordered last Full Year 2011, and trying the order for 2012.Once I selected Full Year 2012, the Renewal field should automatically put a YES otherwise NO.

      How can I get the last value from the previous transaction of the customer so that the Renewal field will automatically populate the answer?

      Note: The subscription is in Text format.

      Thank you so much

        • 1. Re: How can I get the last value record

          There are two approaches you can experiment with:

          GetNthRecord is a function that can access a different record in the same found set. If your found set consists of just this customer's present and past subscription records, GetNthRecord ( Field ; Get ( RecordNumber ) - 1 ) will access "field" in the preceding record. This approach can be a bit fragile as the current found set and sort order will control which record is accessed by this function.

          You can possibly set up a relationship matching by a customerID field with a specified sort order so that you can access data from the customer's previous subscription record. This approach does not rely on the current status of the found set, so while a bit more work to set up, your link can be used in more situations and is much less likely to produce an erroroneous result.

          Say you have a serial number field to uniquely identify each record: pkSubscriptionID and a ClientID field to identify the client.

          Subscriptions::ClientID = SubscriptionsSameClient::ClientID AND
          Subscriptions::pkSubscriptionID ≠ SubscriptionsSameClient::pkSubscriptionID

          Will match to all previous subscription records by the same ClientID. The second clause with  ≠, insures that any given record does not match to itself. If you sort this relationship in descending order by pkSubscritpionID, a reference to SubscriptionsSameClient::Field will refer to the client's previous subscription record if one exists.

          Note: SubscriptionsSameClient is a second table occurrence of Subscriptions created by selecting Subscriptions and clicking the button in Manage | Database | Relationships that is marked with two green plus signs.

          • 2. Re: How can I get the last value record

            Thank you so much =)