Jump to content

IsValid & Is Empty


This topic is 5767 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

This topic is 5767 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.