Surpress #VALUE! for non existing entries

I have a column “InfoSichhBelehrung”, which is linked to a table, where users can upload a certificate and a date is stored in the column Belehrungsdatum.
Of course, not each and every user has the needed certificate at the beginning of a year. Some have it, some have an old one from last year or even sooner and some new folks dont have one at all, as they need to go through training yet.

If I check, if a user has a valid certificate for the current year, I get #VALUE! for all user rows, that do not have a corresponding entry in the certificates table
Formula is as follows:if(year({InfoSichhBelehrung.Belehrungsdatum})=year(now()),1,0)

This gives back 1 for this years certificates, 0 for those uploaded earlier and #VALUE! for those users, who do not have one in the linked tables column.

All i want is to supress the #VALUE! and return “” instead.

How can I reach that? isempty() does not work on linked columns, as it seems.

Why do i want to suppres this? Because if I do statistics with those values, I always have that ugly #VALUE! as one group of entries. And since one cannot manually change the descriptive legend names on those graphics generated there… yeah nothing I can send to people.

Hi @Gerald.

I think the problem you have is to try to get the values from the referenced table. This will not work. You have to create a second column beside the reference column with the value you want to validate. Then you can run the formula on the second column.

I disagree. The error here is simply, that seatable counts non existing values as error, when it comes to linked columns, instead of just returning null or an empty string.

You need to get something back, that is checkable in a formula. i would even be happy, if “#VALUE!” would be checkable by an if(…something) call. But it is not, and that is the error here.

Anyhow. By adding another column to the referenced table and doing the date check there, filling in a 1 for a current certificate and 0 for an old, I could then compare the resulting 1 for a current certificate in an if() call and return a string for those and antoher string for all others in the main table.
Funny enough, that worked and gave no #VALUE!

But that is really a dirty workaround for a problem, that should not exist in the first place.

This topic was automatically closed 2 days after the last reply. New replies are no longer allowed.