Pulling data from the previous repeat

[AnnaSan1]

I have a form I created using an existing hosted layer feature on AGOL. The point layer is of water meter locations with a related table for monthly readings. Currently, my form is set for the field crew to type in the current month's reading, type in the Previous month's reading, and multiple that by a multiplier(a field present in the point layer) to get the value of GallonsUsed for the month. 

However, ideally, I wanted to somehow pull the Previous's month's reading(the value obtained from the previous repeat of that location) into a new repeat to avoid having to look up and type that value in, thus getting rid of the Previous month reading field altogether.

This is what I currently have in my calculation column:

Is what I want possible? Or we stuck with manually entering the values when creating a new repeat?

Thank you

[patricksowers1]

One option depending on your workflow might be to use Collector in conjunction with Survey123. I havent tried it, but you could try and create a hosted feature service view(doesnt use credits) using the Join Fields analysis in AGOL that grabs the most recent meter reading date and joins that to the meter feature service. You could then use the survey123 URL scheme in a Collector pop up to push the meter serial number, most recent meter reading, and ideally the GlobalID to Survey123? 

Join Fields settings:

[Anonymous User]

Hi Anna,

Are you loading and editing the existing parent record via the Inbox that contains the repeats? If so, at the time of collecting the new repeat record (current month), is the previous months data already loaded in a previous records in the repeat?

Could you use the max() function outside the repeat questions (in parent) layer to get the max value of the data already collected in current month, and then reference that value via a calculation to populate the previous month value (as it should always be the max value when loaded via Inbox?) when adding a new record?

If you could share your xlsx form I could take a closer look, but am hoping the method described above should work.

Phil.

[AnnaSan1]

Phil,

Sorry for the delay in response! I just got back to work this week and have been playing catch up. 

How should I send you a copy of the .xlsx form? 

I am loading the existing data via Inbox.

The workflow I have at the moment is to:

1) open the previous month's repeat

2) copy value of "CurrMonReading" field

3) Create a new repeat.

4) Paste copied value into the "PrevMonReading" field. 

 If I can get the Previous month's reading to autofill into a new repeat, than my users won't have to worry about all this back and forth.

[Anonymous User]

Hi Anna,

I have sent you a private message with my email details so you can send the files.

Phil.

[Jim-Moore · Accepted solution]

Hi Anna

Thank you for providing the XLSForm for your survey. 

The max() function Philip Wilson mentioned can be used to return the maximum value from a field in a repeat. However, in this case each time a new repeat record is added and the current meter reading entered, the max() function will read in this new value (essentially overwriting the previous maximum value). This then breaks the subsequent GallonsUsed calculation. The way you've designed your survey (i.e. user manually enters the previous reading each time a repeat is added) is currently the best option to ensure the form works.

We have an open enhancement request to introduce indexed repeat functions (e.g. return value from first repeat, last repeat, repeat x of y, etc.). This would allow you to 'look up' the latest meter reading and use it in another calculation. We will add to this thread any updates on the progress with this enhancement.

Best regards,

Jim

[Anonymous User]

When adding the new record within the repeat; could you use the once() function when pulling the max() function of the repeat in order to retain the calculation that occurs? Something like this below? Maybe you would need to use both entry columns?

(once(max(${lastMeterRead}))) - (${currentMeterRead})

[DougBrowning]

I was just working on this today actually.  And what Mike listed is what I did.

Field 1 - outside of repeat gets calc max(0, max(${repeatfield}))  the 0 is to handle the very first one.

Field 2 - inside the repeat gets once(${Field1})  That way when a new repeat is added it gets the held max value just that once.  Then when max() changes no biggie.  Once a new max is entered Field 1 changes and then in turn is used on repeat 3.

In once case I went ahead and filled in the last value for the user.  In others I left the field viable so the user can see the value from the last repeat right there.  I am still a bit concerned about users going 3 repeats back and "fixing" something.  This may then fall apart.

[AlexFazzino]

Hi @Jim-Moore, I was wondering if there has been any progress with the open enhancement request for indexed repeat functions? 

[Jim-Moore]

Hi @AlexFazzino two new functions were introduced in Connect 3.12 (2021) for working with repeat indexes: position(..) and indexed-repeat(). Please refer to the following documentation:

Return the index of a repeat record

Use values from indexed repeats