Skip to main content
Solved

How to make this work - fixing date in a concatenating

RonniEloff_VKDe
Forum|alt.badge.img+17

I know how to make my date show correctly using DATETIME_PARSE(DATETIME_FORMAT({EDate}, 'MM/DD/YY'))

And I know how to create a new string by using this formula

{EDate (from Events For Sync)}& "- "&{EID#}

But what I can't figure out is how to make the first formula work with the second formula into one formula that works.

I've tried different ways but nothing seems to work and I am stumped

This may seem easy for some - but I am frustrated that the date field when you have it set for one way doesn't carry the content over when used in a formula.  But whatever.

Best answer by kuovonne

> Why do you want to create a date form a string and then make it a string again?

This is surprisingly common. I think it happens when people do not understand the vocabulary.

I think the original poster wants something like this. (Sorry for the horrible formatting. I cannot format text as code on my phone.)

 

 

CONCATENATE(

DATETIME_FORMAT({EDate}, 'MM/DD/YY'),

  "- ",

  {EID#}

)

View original
Did this topic help you find an answer to your question?

6 replies

Databaser
Forum|alt.badge.img+19
  • Databaser
  • Inspiring
  • 866 replies
  • October 22, 2023

Why do you want to create a date form a string and then make it a string again?

Jan - Databaser
kuovonne
Forum|alt.badge.img+27
  • kuovonne
  • Brainy
  • 6006 replies
  • Answer
  • October 22, 2023

> Why do you want to create a date form a string and then make it a string again?

This is surprisingly common. I think it happens when people do not understand the vocabulary.

I think the original poster wants something like this. (Sorry for the horrible formatting. I cannot format text as code on my phone.)

 

 

CONCATENATE(

DATETIME_FORMAT({EDate}, 'MM/DD/YY'),

  "- ",

  {EID#}

)

RonniEloff_VKDe
1 person likes this
  • RonniEloff_VKDe
    RonniEloff_VKDe
Databaser
Forum|alt.badge.img+19
  • Databaser
  • Inspiring
  • 866 replies
  • October 22, 2023

I didn't want to jump to conclusion, but 🤷‍

The formula from Kuovonne: 

 

CONCATENATE(DATETIME_FORMAT({EDate}, 'MM/DD/YY'), "- ", {EID#})

 

Jan - Databaser
RonniEloff_VKDe
1 person likes this
  • RonniEloff_VKDe
    RonniEloff_VKDe
RonniEloff_VKDe
Forum|alt.badge.img+17
Databaser wrote:

Why do you want to create a date form a string and then make it a string again?


Reason is because we needed a master unique identifier to put - EventShortName+Date as the way to link - Events, Registration/Attendance, Consent forms, the photos shoot from those events and track them back to someone specific.

We store events in one base, Registration in another base, Digital Consent forms in another base (along with other legal stuff) Photo shoot information (links to the photos+meta data) and the outside view for the person to pick up their photos if they requested a copy.

 

RonniEloff_VKDe
Forum|alt.badge.img+17
kuovonne wrote:

> Why do you want to create a date form a string and then make it a string again?

This is surprisingly common. I think it happens when people do not understand the vocabulary.

I think the original poster wants something like this. (Sorry for the horrible formatting. I cannot format text as code on my phone.)

 

 

CONCATENATE(

DATETIME_FORMAT({EDate}, 'MM/DD/YY'),

  "- ",

  {EID#}

)


Thank you and yes I do know the vocabulary I wanted to be able to create master unique identifier based on multiple AT table Column A fields (Which is one of the most annoying issues with AT that it doesn't support something Excel and MySQL both support.

Anyway workflow is

EventShortname(string)+Date of Event(date field) is the EDate identifier

People in the system (First Name/Last Name/Email yadyyada) Email is the Field A which we use for sync link/lookup to do our registration

Email use to create the specific entry for the person Registered for the event and is assigned a number which adds the EDate to create another identifier EID

All Attendees must have a Digital asset Consent form on file which is DACID to identify the person within the system

DACID+EID is turned into a QR Code that is added to the person's Badge so it can be digitally scanned and added to the photos taken by any of the photographers or even people just taking pictures with their cellphones at the event. 

This way when the all the photos are dumped up in the dropbox, we can quickly pair them up with who they are of.  Makes life a lot easier at very large conferences.

But what was happening in the formula it was reverting back to the ugly 2023-10-21T00:00:00.000Z - which defeated the purpose of trying to make a short unique identifier 

RonniEloff_VKDe
Forum|alt.badge.img+17
Databaser wrote:

I didn't want to jump to conclusion, but 🤷‍

The formula from Kuovonne: 

 

CONCATENATE(DATETIME_FORMAT({EDate}, 'MM/DD/YY'), "- ", {EID#})

 


But you got me thinking about this from one of the other requests - and I simplified the work flow a bit, and I might actually change how the unique identifier is created.  Plus I really didn't need the slashes I thought it would be easier for others to read, but I think I will show it to the photo team and asked them with / or without the /.

Heck just getting them using dropbox to put the images into was a major milestone so we didn't have to upload everything into airtable through an interface (which would have cost us) or form (which with 100s of pics was a PIA) 

Databaser
1 person likes this
  • Databaser
    Databaser

Reply


Most helpful members this week

Terms & Conditions