5 Replies Latest reply on Apr 25, 2017 9:30 AM by beverly

# Go to first field in a found set with a non blank record

Hi there

I have a found set of around 20 records following a complex find.  I'm gathering variables on high and low values in the field 'Money'.  I've also got another field called 'MoneyAdj' which is a Case statement based on 'Money'.  As a result, around 6 of the found set in this field 'MoneyAdj' are empty values, which is correct.

However, from the 14 remaining, I need to show the lowest.  Easy right?  Just sort the found set ascending by 'MoneyAdj' and Go To Record [First].  Nope.  The blank rows come first, so my plan is scuppered here.

Is there any way of getting to the first non blank record in the 'MoneyAdj' field (which is the lowest actual value) without having to redo the find and omit routine I've already done to get to this stage?

I was thinking something like Go To Record by calc GetNthRecord (NotIsEmpty(MoneyAdj) but haven't had that working so far.

This is being done in a looping routine in a script and I'd like to keep that as light as possible.

Hope that makes sense!

• ###### 1. Re: Go to first field in a found set with a non blank record

This is one of those tricky sorting thingies.

You can have another field for the sort that isn't even shown on the layout.

If ( <<your calc field here>> = 0 ; 6,000,000 // or something ridiculously large

; <<your calc field here >> )

Sort on that field.

beverly

• ###### 2. Re: Go to first field in a found set with a non blank record

Ha - Love it! Tricky sort thingies...

Although yes, the solution will work (no chance of any big numbers in the 'Money' field!)

Thanks for that Beverly

• ###### 3. Re: Go to first field in a found set with a non blank record

The list function or Summary field List of will not return empty fields.  If the list is sorted in ascending order, the first value in the returned list should be the value that you are looking to get.

• ###### 4. Re: Go to first field in a found set with a non blank record

You can reduce the calc down to a simple IsEmpty(MoneyAdj).  This will return a 1 if empty or a 0 is not empty.  then Sort on this field in ascending order and then the MoneyAdj field in ascending.

• ###### 5. Re: Go to first field in a found set with a non blank record

so true for a multi-field sort!!

Thanks, RG!

beverly