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

[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?

[GuidoPreite]

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

[ivan_apps]

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.

[SteRe]

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.

[JerryVividMiM]

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!

[JerryVividMiM]

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

Thanks for your thoughts!

[SteRe]

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

1. Create a string field
   1. create a flow triggered on record create to concenate the other fields and write into your field OR
   2. do concenation with JS and have this as form logic. would be real time but only triggered from forms.
2. Creating a calculated field but maker portal shows a message this type might become deprecated in future
3. (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.