How do I calculate text fields to output data from multiple related records?

1163
2
Jump to solution
11-07-2017 02:52 PM
RogerFarmer1
Occasional Contributor

I have a Pole Inspection survey with a repeat for possible multiple cross arms. There is also a 'Design Use Only' field which is pre-populated with conditional calculations based on certain fields of a like-for-like replacement. In the case of there being more than one record for multiple cross arm condition failures, I want to be able to extract ALL of the failed cross arms. Presently it works only for the cross arm record that is currently showing on the form.

Above is the first cross arm; showing correctly in the output field:

Second cross arm on the form:

Showing correctly on the form (but the first record has dropped off):

The desired output should have BOTH cross arms:

Calculation is: (highlighted section calculates if cross arms fail inspection)

"GIS #" + ${PoleNumber} + "\n" + ${PlanPoleNumber} + "." + "\n" + if((selected(${TagFitted}, 'Red')) or (selected(${PoleTopCondition}, 'Fail')) or (selected(${PoleGroundCondition}, 'Fail')) or (selected(${PoleOtherCondition}, 'Fail')), 'RMV. ' + round(${Height}, 1) + 'm ' + pulldata('Pole_SubtypeCD','Description','Code', ${Material}) + "\n", '') + if((selected(${CrossArmCondition}, 'Fail')), 'RMV. ' + pulldata('Pole_Crossarm','Description','Code', ${CrossArmType}) + ' ' + pulldata('OperatingVoltage','Description','Code', ${CrossArmVoltage}) + ' ' + pulldata('Pole_Crossarm_Size','Description','Code', ${CrossArmSize}) + ' @ ' + ${CrossArmHeightAGL} + "\n" + 'N. ' + pulldata('Pole_Crossarm','Description','Code', ${CrossArmType}) + ' ' + pulldata('OperatingVoltage','Description','Code', ${CrossArmVoltage}) + ' ' + pulldata('Pole_Crossarm_Size','Description','Code', ${CrossArmSize}) + ' @ ' + ${CrossArmHeightAGL} + "\n", '') + if((selected(${Lean}, '1')), 'N. STRAIGHTEN' + "\n", '') + if((selected(${LowerTop}, '1')), 'N. LOWER POLE TOP ' + ${LowerTopDistance} + 'mm' + "\n", '') + if((selected(${PossumTrap}, '0')), 'N. FIT POSSUM GUARD' + "\n", '') + if((selected(${UGCable}, '1')), 'N. REFIT UG ' + ${UGCableVoltageCategory} + ' CABLE' + "\n", '') + if((selected(${Telco}, '1')), 'N. REFIT ' + ${TelcoOrientation} + ' TELCO' + "\n", '') + if((selected(${Trees}, 'WhenMaintained')), 'N. TRIM TREES' + "\n", '') + if((selected(${Access}, 'ContactOwner')), 'NB. CONTACT LAND OWNER FOR ACCESS: ' + ${LandOwner} + "\n", '')

I have attached the xlsx form and a sample of the desired output.

Thanks,

Roger Farmer.

0 Kudos
1 Solution

Accepted Solutions
by Anonymous User
Not applicable

Hi Roger,

Unfortunately the calculation you are trying to build across repeat records is not currently supported as we do not support indexed-repeats. We have an open enhancement request for this issue and I have added your comments and use case to it.

As per you comments, only the fields from the repeat record that is being displayed (active) will be used in the calculation, as each repeat record has the same field name and schema, so in order to be able to identify them differently, the repeats need to be indexed.

However we do currently support the following aggregate functions across repeats: sum, count, min, max.

Regards,

Phil.

View solution in original post

0 Kudos
2 Replies
by Anonymous User
Not applicable

Hi Roger,

Unfortunately the calculation you are trying to build across repeat records is not currently supported as we do not support indexed-repeats. We have an open enhancement request for this issue and I have added your comments and use case to it.

As per you comments, only the fields from the repeat record that is being displayed (active) will be used in the calculation, as each repeat record has the same field name and schema, so in order to be able to identify them differently, the repeats need to be indexed.

However we do currently support the following aggregate functions across repeats: sum, count, min, max.

Regards,

Phil.

0 Kudos
nejibco
New Contributor III

Hi Philip,

Will Survey123 3.0 support indexed-repeats, position() or/and join() functions ?

Regards