Unanswered

Concatenate a date and a text column into a new field in the same row

(0) Share

Report

Posted on by JerryVividMiM

In my dataverse I have a Daily Report table with a Date and Room field, which together form the natural key of the table. I would like to merge the Date and Room into 1 new field and preferably use that as the primary column.

I am unable to do this in the formula column because the date cannot be converted to text. I also can't do it with a business rule because you can't merge two fields with it.

Is there any way to solve this?

Categories:

Microsoft Dataverse with Power Apps

I have the same question (0)

All responses (6)

Answers (0)

Guido Preite 1,488 Super User 2024 Season 1 on at

Like (0)

Report

you will need a Plugin or probably a Power Automate flow (if is ok to be an asynchronous process)

Was this reply helpful? Yes No
ivan_apps 2,187 Moderator on at

Like (1)

Report

You can create the format in JavaScript as an onchange or onSave event. It will only fire if using a MDA form if that works for you.

otherwise you could use a auto-number field and format it to the date you want, granted you want today’s date. Then you can use a formula column to merge the two fields since auto-number will be text.

Was this reply helpful? Yes No
SteRe 254 on at

Like (1)

Report

Hi @JerryVividMiM ,
what's the target of your request? Do you need some "identifier" to be shown to the user in frontend or is it more about preventing from having 2 rows for the same date/room combination? If it is just the second, setup an alternate key containing the 2 columns.

Was this reply helpful? Yes No
JerryVividMiM 33 on at

Like (0)

Report

I am fairly new and not (yet) familiar with JavaScripting. I should delve into that. It's strange that a function to convert dates into text is not standard though.

I also want users to be able to use a date other than today.
Thanks for your thoughts!

Was this reply helpful? Yes No
JerryVividMiM 33 on at

Like (0)

Report

It is intended both as a natural key and for recognisability. I had indeed already found the key alternatives.
Thanks for your thoughts!

Was this reply helpful? Yes No
SteRe 254 on at

Like (0)

Report
Hi @JerryVividMiM ,
as you wrote you'd like to use it as natural identifier too it would be nice to have a dedicated field then (maybe in addition to the key preventing from duplicates). I had a look how to setup such kind of column and there are several options
Create a string field
create a flow triggered on record create to concenate the other fields and write into your field OR
do concenation with JS and have this as form logic. would be real time but only triggered from forms.
Creating a calculated field but maker portal shows a message this type might become deprecated in future
(also new to me) use field type formula: https://learn.microsoft.com/en-us/power-apps/maker/data-platform/formula-columns
I've never used the last option so far, but looks like a good approach.

Was this reply helpful? Yes No