concatenate number with a text string, or convert number to text string

Like (0) Share

Report

Posted on 31 Aug 2017 03:58:14 by nickduxfield

497

I have a lot of occasions where I want to patch a single line of text with a combination of an ID column to generate some Service Code. I was hoping to use a simple Concatenate.

[Text-]-[Text-][ID]

I'm able to convert numbers to Text using Value()

Categories:

General Questions

All responses (4)

Answers (2)

nickduxfield 497 on 01 Sep 2017 at 03:33:32

Like (0)

Report

Re: concatenate number with a text string, or convert number to text string

Exactly, I love how these forms are setup with this OnSubmit and like.
Verified answer

CarlosFigueira on 31 Aug 2017 at 17:03:32

Like (0)

Report
Re: concatenate number with a text string, or convert number to text string

If the ID is auto-generated when you create a new element in the NewForm(...), you should be able to get it on the OnSuccess action of the form, by looking at the LastSubmit property of the form - it contains the item that was created after submitting the form. From its description:
LastSubmit – The last successfully submitted record, including any server generated fields.
This property applies only to the Edit form control.
If the data source automatically generates or calculates any fields, such as an ID field with a unique number, the LastSubmit property will have this new value after SubmitForm successfully runs.
The value of this property is available in the OnSuccess formula.
nickduxfield 497 on 31 Aug 2017 at 08:14:13

Like (0)

Report
Re: concatenate number with a text string, or convert number to text string

Thanks Carlos, I tried that but initally encountered expectations of placeholders, as in Text Date syntax.

The context is as follows.
This is from a SharePoint list.
It is a New Form that creates service codes.
A simple concatenate together with the SharePoint Item ID, is simple.
the elements that form part of the Concatenate are pulled from multiple lists, but am having trouble getting the ID, as it is autogenerated.
Below are some methods I had to reach that point.
Example service code
SC-KBCE-ABA-66
[SC-][CompanyABBR-][ServiceType-]-[ID]

The method I added a Confirm Save step with a button so I could get the ID from the SharePoint list visible in a Text field. Then I would patch, if confirmed. WORKS
Patch('Service Code',GalleryServiceCodes.Selected,{Title:Concatenate("SC-",LookUp(RegisteredClientsCollection,Company=DataCardValue7.Selected.Value,ShortCode),"-",LookUp(ServiceTypesCollection,DataCardValue75.Selected.Value=Title,ShortCode),"-", DataCardValue29.Text)});UpdateContext({ServiceCodesDisplayFormVisible:false,ServiceCodesGalleryVisible:true,ServiceCodesEditFormVisible:false,ServiceCodesSaveConfirmationVisible:false,NewServiceCode:false});ClearCollect(ServiceCodesCollection,'Service Code')

My actual wish was to patch on the first save, but Text(ID) on its own isn't recognised.
Patch('Service Code',GalleryServiceCodes.Selected,{Title:Concatenate("SC-",LookUp(RegisteredClientsCollection,Company=DataCardValue7.Selected.Value,ShortCode),"-",LookUp(ServiceTypesCollection,DataCardValue75.Selected.Value=Title,ShortCode),"-", Text(ID))});UpdateContext({ServiceCodesDisplayFormVisible:false,ServiceCodesGalleryVisible:true,ServiceCodesEditFormVisible:false,ServiceCodesSaveConfirmationVisible:false,NewServiceCode:false});ClearCollect(ServiceCodesCollection,'Service Code')

I tried to add specificity, but was wondering as Patch already was specific about which record I'm refering to in 'GalleryServiceCodes.Selected'. My new more specific code does work, but only on confirmation step, not in one step as in a New Form.
Patch('Service Code',GalleryServiceCodes.Selected,{Title:Concatenate("SC-",LookUp(RegisteredClientsCollection,Company=DataCardValue7.Selected.Value,ShortCode),"-",LookUp(ServiceTypesCollection,DataCardValue75.Selected.Value=Title,ShortCode),"-", ID)});UpdateContext({ServiceCodesDisplayFormVisible:false,ServiceCodesGalleryVisible:true,ServiceCodesEditFormVisible:false,ServiceCodesSaveConfirmationVisible:false,NewServiceCode:false});ClearCollect(ServiceCodesCollection,'Service Code')
Verified answer

CarlosFigueira on 31 Aug 2017 at 05:10:09

Like (1)

Report
Re: concatenate number with a text string, or convert number to text string

The function Value converts from text to numbers; you need to use the Text function instead. With that, you should be able to use the Concatenate function to do your concatenation:
Concatenate("text1", "-", "text2", "-", Text(234))