I'm trying to write a calculation that determines, out of 10 fields in linear order, which is the first one to have a valid entry that is not empty. I also need, by the way, to know which is the last one to have a valid entry.

Here's the scenario:

Fields 1-10, plus First Entry and Last Entry

If all the fields are populated, I want First Entry = Field1 and Last Entry = Field10

If only Field10 is empty, I want Last Entry = Field9

If only Field1 and Field2 are empty: First Entry=Field3, Last Entry=Field10

If 1 is empty, 2 is populated, 3 and 4 are empty, 5 is populated, 6 is empty and 7 is populated: First Entry=Field2, Last Entry=Field7

I've been playing around with IsValid and IsEmpty, in a Case function, but can't seem to hit on the right combination.

Any ideas?

Thanks...

Susan

You could try something like:

Let ( [

list =

Case ( not IsEmpty (Field1) and IsValid (Field1) ; Field1 & ¶ ) &

Case ( not IsEmpty (Field2) and IsValid (Field2) ; Field2 & ¶ ) &

Case ( not IsEmpty (Field3) and IsValid (Field3) ; Field3 & ¶ ) &

...

Case ( not IsEmpty (Field10) and IsValid (Field10) ; Field10 )

] ;

Substitute ( LeftValues ( list ; 1 ) ; ¶ ; "" )

)

for First Entry. LastEntry would be the same, except using RightValues. This assumes there are no carriage returns entered into the fields.

However, the question indicates that you may have the wrong data structure. Consider putting those fields in a related table, with a record for each.

Thank you... it does exactly what I need.

I know my structure could be better, but this work-around saves me tons of time and I don't have to redo the whole project. Hopefully I'll be smarter at the beginning of my next one!

Thanks again.

Susan