2 Replies Latest reply on Jan 18, 2015 3:18 AM by MartonNagy

    How to calculate repeating values in a related date field?

    MartonNagy

      Title

      How to calculate repeating values in a related date field?

      Post

      Hi,

      I am pretty new to filemaker and I am struggling with an easy problem. I have a parent table containing participants and I have a child table with all their experimental appointments. For a given participant there can be several experimental appointments with date fields (an other infos).  I would like to have a calculation on the layout of my participant infos which would show how many NOT REPEATING dates they have (sometimes one participant has many experimental appointments on the same date and I want to see how many different occasions were they visiting our lab.).

      I could easily calculate a sum for all the related date fields, but I can't figure out how to eliminate the repeating dates.

       

      Thanks for all the help in advance!

        • 1. Re: How to calculate repeating values in a related date field?
          philmodjunk

          The ExecuteSQL() function can calculate this (Note this is NOT the script step of nearly the same name). A new way to count unique values in FileMaker 12

          You could set up a conditional value list of your dates using your existing relationship as the means to limit the value list to just the dates for this participant. Then ValueCount ( ValueListItems ( Get ( FileName ) ; "YourValueListNameHere" ) ) will return the number of unique date values for your participant.

          You could also modify your data model from:

          Participants-----<Appointments

          to:

          Participants-----<appointments>-----Dates

          Appointments::_fkParticipantID = Dates::_fkParticipantID AND
          Appointments::Date = Dates::Date

          where you limit a given record in Dates to a single record for any unique combination of Date and ParticipantID. Then you can count the related records in Dates. The way you'd use this is to enable "allow creation..." for Dates and use this script every time you create a new appointment record:

          Set Field [Dates::Date ; Appointments::Date ]

          This step creates a new record if one does not already exist, but produces no net change if a related record already exists so your duplicates never appear in the Dates table like they do in Appointments.

          • 2. Re: How to calculate repeating values in a related date field?
            MartonNagy

            Hi Phil,

            Since I am new to filemaker I chose the second option with the conditional list. That looked the easiest among the options. It worked fine! Thank you so much for your quick reply!

            Best,

            M.