Combining two fields of different data types

My table (which happens to be called ‘Notes’ but could be anything really) is tracking Created Date/Time and Created By, in two different fields. When I display the table view, I don’t want to waste two columns to show this info. So I would like to create a field with a calculated result that looks something like this:

William Porter 3/3/2021 11:30pm

or thereabouts. I don’t see a way to create a formula field that combines a date/time value and a text value.

If there’s a totally different way to do this, that would be fine too. I don’t really care about the field – I just want users to be able to see at a glance who created the record and when, without taking up 2 whole columns to do it.

William

Unfortunately we don’t yet have a way to use date inside of text formulas. This will be possible eventually.

For now if you want a workaround, here’s what you can do.

  1. Install the “Custom Javascript” pipe.
  2. In your Record Rules use the “Concat String with Custom Date” and set the string to logged in user’s name and the Date to the date field.

Finally in the response set the text field you have to the response value of “Result”

Here you can see the outcome:

Feel free to dig in and have fun with the Javascript part to customize the output however you wish.

Thanks, Moe – especially for the step-by-step guide. Was your reply pulled from an old response? I ask because the options provided by the Custom Javascript pipe didn’t include one with the precise name of the one in your screenshot (“Concatenate String with Custom Date from Custom Javascript”). Thankfully the options are few and it was not hard to see that this must be the one to use: Concatenate Formatted Date and String from Custom Javascript.

I can make this work as is, but I’m going to look the gift horse in the mouth here and ask a follow-up. This solution seems to report the last modified metadata for the record. I could work with that, but ideally, what I’d like to show is the creation metadata (current timestamp and user).

So I played around with that pipe. First I added a condition, telling the pipe to run only if the target field is blank. That seems to work, but now the problem is the result says “Last updated by William Porter 03/04/2021” – which is misleading. Should say “Created”.

So then I tried changing the trigger for the pipe from Update to Insert Record. But now it doesn’t work. It will insert the logged in user’s name into the target field, but that’s all it does. It doesn’t generate the result I was expecting, that is, something like “Record added by William Porter on 03/04/2021”.

Is it possible to do that? I can live with the updated metadata if that’s necessary; not a huge problem. THANKS AGAIN.

William

(screenshot below shows results as I tried the various approaches I described above)

@WilliamPorter, my pleasure. And I think I have changed the name after the screenshot, but you did choose the correct pipe.

To clarify, you can customize this pipe and have it return any details you wish.

Inside your app you can go to the pipe configuration and in the last line of that pipe you can change the text to whatever you wish.

This code here is evaluabted as plain JavaScript and you can use any JS.

In the last line you can see:

"Created by: " + "{string}" + " on " + regularMonth + "/" + day + "/" + year

In your case, if you simply wanted to show the user name and date this event occured, you can replace the entire code with this and not even pass the Date as a parameter. Assuming you only trigger this when records are created.

var date = moment().utcOffset('-0800').format('LLL'); 
"Created by: " + "{string}" + " on " + date

What we’re doing here is simply passing a string (user’s name) and saving the date/time to current date/time in EST. You can change the format, timezone etc.

For example:

var date = moment().utcOffset('-0800').format('LTS'); // 9:58 AM

Here’s a list of format options:

.format('LT');   // 9:58 AM
.format('LTS');  // 9:58:14 AM
.format('L');    // 03/04/2021
.format('l');    // 3/4/2021
.format('LL');   // March 4, 2021
.format('ll');   // Mar 4, 2021
.format('LLL');  // March 4, 2021 9:58 AM
.format('lll');  // Mar 4, 2021 9:58 AM
.format('LLLL'); // Thursday, March 4, 2021 9:58 AM
.format('llll'); // Thu, Mar 4, 2021 9:58 AM

Getting a bit carried away. But as you can see you can pass any values and do anything you wish with those values. Just let me know what you’d like the output to be and I’d be happy to assist further.

Edit: I added a new method to accomplish this. “Concatenate with Moment.js”

This is great, Moe. Thanks for getting back to me. I added a condition (if Meta field is Blank) and per your instructions I edited the last line of the javascript code so it says “Created By” instead of “Updated by”. and I’ve got exactly what I want. Terrific. THANK YOU!