Hiding empty date fields

1

I have a form that shows contact details at a glance. In that form I have a section that shows family members. I have a single text display box that has all the fields I need. I have checked the collapse empty lines.

The DB allows for 3 children entries. For each child there are three fields (Children01FN, Children01LN and Children01DOB. My formula is as follows:

Children01FN+" "+Children01LN+" ("+age(Children01DOB)+")"+cr()+Children02FN+" "+Children02LN+" ("+age(Children02DOB)+")"+cr()+Children03FN+" "+Children03LN+" ("+age(Children03DOB)+")"

However, most families do not have three children. If a record only has one child the text display shows:

Fred Blog (12)
 (2023)
 (2023)

The fields for Children02 and Children03 are all empty. It seems that the collapse command doesn’t work with date fields?

How can I collapse empy date fields?

2

You need a conditional that suppresses unnecessary calculations and text elements if the name of a child is empty.

In your code even an empty name results in a calculation of the age. So that line is NOT empty.

3

Thanks for that. Would the ?( formula acheive that?

Where would I find syntax for suppressing empty fields showing on a form?

4

I took your code and modified it, e.g. shortened the field / variable names from Children to “Ch”. The 1st line checks if a first child exists. The Lines 2 and 3 check if there exists a child 02 or a child 03. If not they replace the formula with an empty string. — You might still have to take care of situations where a child exists whose name you know but not the date of birth. You certainly do not want to read this child to be 2023 years old.

?(length(Ch01FN)>0,Ch01FN+" "+Ch01LN+" ("+age(Ch01DOB)+")","")+
?(length(Ch02FN)>0,cr()+Ch02FN+" "+Ch02LN+" ("+age(Ch02DOB)+")","")+
?(length(Ch03FN)>0,cr()+Ch03FN+" "+Ch03LN+" ("+age(Ch03DOB)+")","")
1 Like
5

Look at functions Sandwich( and Yoke(
There might be others that simplify the concatenation.

6

Yes, sandwich(

Children01FN+" "+Children01LN+" ("+age(Children01DOB)+")"+sandwich(cr(),Children02FN+" "+Children02LN+" ("+age(Children02DOB)+"),"")+sandwich(cr(),Children03FN+" "+Children03LN+" ("+age(Children03DOB)+")","")
7

Thanks for that guys! Really appreciated it.

I couldn’t get the sandwich( function to work. It still left the dates showing (there is a " missing, which I corrected).

@KJM solution worked perfectly, even when there where no children at all for a record.

I’ve really been enjoying using PanX. I’m building a church database, that’s a culmination of Ninox and spreadsheets. So far so good. The only challenge is when you don’t know what function will do what you need and this forum certainly comes to the rescue!

8

For the concatenation of the names the yoke function is better than the sandwich function. If you concatenate two empty name fields, sandwich results in the connecting “root” character; yoke’s result is an empty string.

1 Like