2 Replies Latest reply on Sep 10, 2014 7:24 AM by WiserGuy

    Multi-value criteria



      Multi-value criteria



      I am streamlining an old certification database and have a question about multi-value criteria, I was hoping for some help with.

      • tbItems::SerialNo
      • tbCerts::ID
      • tbCerts::SerialNo (relation to the above)
      • tbCerts::CertType
      • tbCerts::CertDate
      • tbCerts::DueDate
      • tbCerts::ReCert

      For one particular item, I can have a list with 50 certificates over the year of various certificate types. The reason for checking the ReCert checkbox is to be able to filter out items which has had the specific certification type recertified, but keeping them in the table.

      If we add a new certificate and there is already an older certificate with the same certificate type, we check the old one manually.

      I'm sure there is a way to check this box automatically if certain criterias are met, I'm thinking of something like:

      1. Checking all records with same SerialNo and CertType in tbCerts
      3. If CertDate for a CertID is older than one that match the above, then check CertRecert

      Would be great if someone could help me with a script, or feel free to point me to other ways of doing this.




        • 1. Re: Multi-value criteria

          You can make a new table occurrence of tbCerts, name it tbCerts|SameType and link it like this:

          tbCerts::SerialNo = tbCerts|SameType::SerialNo AND
          tbCerts::CertType = tbCerts|SameType::CertType

          You can specify a sort order for this relationship that sorts tbCerts|SameType in Descending order by CertDate

          then, when your context is a record in tbCerts, a reference to tbCerts|SameType will refer to the most recent tbCerts record for the same item of the same type.

          But be careful of setting a field such as CertRecent to mark the most recent record of this serialNo and type as you will need to clear this value for the previous record as well as set it for the most recent record. The above relationship, BTW, can be used with a portal filter to drop out the "old" certifications and keep the most recent ones without any additional data being recorded in the fields. You'd use this portal filter:

          tbCerts::ID = tbCerts|SameType::ID

          Caulkins Consulting, Home of Adventures In FileMaking

          • 2. Re: Multi-value criteria

            Thanks Phil,

            Worked perfect. The portal filter was a way more elegant method than the checkbox.