Double trouble while use CONCATENATE(): weird display of date and data

[Igor-just-igor]

Hello,

To save space in a card view, I created a virtual column to store as text concatenated data that are in multiple columns. I did this before, but it isn't working this time.

What should be "13/05/2023" comes out as "N13/05/2023" (no idea where the character is coming from). I tried formatting the date, it does change the order of month and day but the N persists.

A ref column, Project, that displays the name of the project does not show up as having the actual value of Project printed out, but then value of the ID of the equivalent row in the original Projects table. I tried:
- [_thisrow][Project]
- LOOKUP([Project], Projects, ID, Project)
No success, the data simply does not show up in the final text.

Here is the code I'm using now:

Concatenate(
[Status], " - ",
ifs([Status]="Completado", TEXT([Finished date], "dd/mm/yyyy"),
ISNOTBLANK([Due date]), TEXT([Due date], "dd/mm/yyyy")),
[Status]="Em curso", TEXT([Start date], "dd/mm/yyyy"),
" - ", [Project])

This is the outcome (the alphanumeric string at the end should be actual words):

Any help is much appreciated. Thank you.

[WillowMobileSys · Accepted solution]

---

@Igor-just-igor wrote:

What should be "13/05/2023" comes out as "N13/05/2023" (no idea where the character is coming from)

---

Why does the Status you are comparing to, "Completado" not match the text in the display string of "Completo"?

Also, your IFS parenthesis do not appear to be correct.  The IFS actually ends on the second line.  I assume the third line is supposed to be included as well?   There is likely NO match giving unexpected results.

Using the IFS in this way - part of a CONCATENATE, you should also always have a default so that you do not get unexpected results.  I would implement the IFS like this:

...
ifs(
    [Status]="Completado", TEXT([Finished date], "dd/mm/yyyy"),
    ISNOTBLANK([Due date]), TEXT([Due date], "dd/mm/yyyy"),
    [Status]="Em curso", TEXT([Start date], "dd/mm/yyyy"),
    TRUE, "Unknown"
),
...

NOTE:  The red are the changed parts from your original expression - removing or adding.  The TRUE part is never expected to be used/executed BUT if there is some unexpected circumstance in your data - then you will be immediately alerted to it, know exactly where the problem is and can fix it.

Your [Project] column is a Ref column?  Yes?

If so then you can get to the Project name (or any other details within that row) by using the "dot" notation - otherwise known as dereferencing the Ref.  It would simply look like this:

[Project].[Name]

Obviously replace [Name] with the correct column within your table you wish to use.

Refer to Dereference expressions for more detail on this.

This might also help - Add references between tables 

[Igor-just-igor]

Thanks for the detailed explanation. Learned a lot.