Forum Discussion

Roy-Wanyoike's avatar
Roy-Wanyoike
Qrew Captain
1 month ago
Solved

Getting an index value from a record

Assuming I have a way to do survey by pushing a button which does a count with a timestamp. I want to get the records list by their attempt time which might be in their chronological order of attempt. The max I do attempt is 3. I understand I can use summary field to get the first and last attempt using the Minimum and Maximum but now the value in the middle is what am struggling to get. Anyone with an idea how to go about it? I will appreciate. 

Relationships
  • AD's avatar
    AD
    1 month ago

    Roy, try this out:  Building off of Kelly's input, finding the "middle" by using the summary field filters where the child record is the one that is not equal to the MAX and the MIN?  Summary Field = [Attempt Date/Time] is NOT equal to [Maximum Attempt Date/Time] and [Attempt Date/Time] is NOT equal to [Minimum Attempt Date/Time]. Of course this should only work when there are 3 attempts per participant since the MAX and MIN will be consuming 2 out of 3 records here.  If there are records with < 3 attempts, this won't work.

7 Replies

  • KellyHayes's avatar
    KellyHayes
    Qrew Trainee
    1 month ago

    Assuming that your the field you are summarizing is called [Attempt Date/Time] and your min/max summary fields are called [Minimum Attempt Date/Time] and [Maximum Attempt Date/Time], create a new summary field: 

     

    Summarize Maximum [Attempt Date/Time] where: 

    [Attempt Date/Time] is NOT equal to [Maximum Attempt Date/Time]

     

    This should give you the second most recent attempt date/time. 

     

  • Roy-Wanyoike's avatar
    Roy-Wanyoike
    Qrew Captain
    1 month ago

    Hey Kelly. Hope you're doing well. 
    Thanks for the insight. Apparently this doesn't work as you can't be able to view the summary fields while creating another summary field. Also comparing the [Attempt Date/Time] which I assume is a field in the summary doesn't give you access to the summary fields. There is a lot of limitations.

    • MarkShnier__You's avatar
      MarkShnier__You
      Icon for Qrew Legend rankQrew Legend
      1 month ago

      You need to lookup any summary fields that you need on the child table down to the child table, by adding them as lookup fields on the relationship. Then they will be available to use in your filter.

      • KellyHayes's avatar
        KellyHayes
        Qrew Trainee
        1 month ago

        Thanks!! I forgot that step, very important.

  • Roy-Wanyoike's avatar
    Roy-Wanyoike
    Qrew Captain
    1 month ago

    I want to get the second record assuming I have three attempts. The first and last I can get using the minimum and maximum but now the second one is where I have the challenge. 

  • AD's avatar
    AD
    Qrew Trainee
    1 month ago

    Roy, try this out:  Building off of Kelly's input, finding the "middle" by using the summary field filters where the child record is the one that is not equal to the MAX and the MIN?  Summary Field = [Attempt Date/Time] is NOT equal to [Maximum Attempt Date/Time] and [Attempt Date/Time] is NOT equal to [Minimum Attempt Date/Time]. Of course this should only work when there are 3 attempts per participant since the MAX and MIN will be consuming 2 out of 3 records here.  If there are records with < 3 attempts, this won't work.