Concatenate 2 fields compose a third one.

[dasigrist]

Hi, 

I have 1 table with 3 fields:
- company_id (integer)

- category_id (integer)
- Unique_identifier (text): I want to concatenate company_id value with _ (underscore) with category_id value. 

I followed the instructions here:  

but with no success. I would like some light on my problem. My code in the datapage is:

<SCRIPT LANGUAGE="JavaScript">

function concatenate()
{
var cat = document.getElementById("EditRecordcategory_rel_id").value;
var company = document.getElementById("EditRecordcompany_rel_id").value;

var unique = company + "_" + cat;

document.getElementById("EditRecordUnique_Identifier").value = unique;

}

document.getElementById("caspioform").onsubmit=concatenate;
</SCRIPT>

I replaced the insertRecord for EditRecord because the above document says: Search and Report DataPage, replace the prefix "InsertRecord" with "EditRecord" in the code. but the code is not in the details page. I put the script in the footer of my "Search and Report Wizard - Configure Results Page Fields".

any help would be great. 

attache there is a screenshot of the page: 

thank you.

[AnchovyXL]

Hi,

You have to convert them first.

CONVERT(VARCHAR(10), [@field:Company_Rel_ID])+"  " +CONVERT(VARCHAR(10), [@field:Category_Rel_ID])

If you want to concatenate the fields in your DataPage, then you can use the Calculated Value. This can also be done at the table level using a formula field so you don't have to do it in your DataPage. Just like this: http://prntscr.com/mamp5s

Hope this helps!

[TsiBiRu]

Hi @dasigrist,

You actually don't need to use any JS code in this, you can just set the form element of your field as a Calculated value, then combine the value of two other fields just like this https://prnt.sc/maajvl

And the output would be like this http://prntscr.com/maakgy , If you want to add spaces you can do this http://prntscr.com/maakuy . This is the example code [@field:Fname] + '  ' + [@field:Lname] , just replace it with the name of your field. And this will be its out put http://prntscr.com/maala9
 

For more details about calculated Fields, you can check this link https://howto.caspio.com/datapages/datapage-components/calculated-values/

I Hope this helps.

Regards,

TsiBiRu

[GoCubbies]

Just to add to what TsiBiRu stated,

In a formula field or calculated value you can concatenate without needing JS. The benefit of a formula field is that it will also concatenate values brought in via import or API. Calculated Values have the benefit of displaying on the submission page.

Also here is another valid way to concatenate the two fields, using the function Space(n) with n being the number of spaces you want between the fields:

[@field:Fname] + Space(1) + [@field:Lname]

[dasigrist]

Sorry guys, but I don't have that option, as you can see by the screenshot attached.

 

also, I remember trying this before with another datapage and I couldn't pass the verify part. Are you sure I can concatenate integer with string. I don't think I can.

Thank you.

[dasigrist]

Hi again,

I tried your suggestion creating a new datapage with the same type of your example. I didn't work either as you can see by the screenshot. I don't think you can concatenate integer+string+integer. 

Thank you for any suggestions.

 

Daniel

[AnchovyXL]

These links are useful, you might want to check them.

Adding Formula Fields: https://howto.caspio.com/tables-and-views/data-types/formula-fields/

Function Reference: https://howto.caspio.com/function-reference/

[TsiBiRu]

47 minutes ago, dasigrist said:

Hi again,

I tried your suggestion creating a new datapage with the same type of your example. I didn't work either as you can see by the screenshot. I don't think you can concatenate integer+string+integer. 

Thank you for any suggestions.

 

Daniel

Hi @dasigrist

You are correct, the reason why you are encountering this error is because you are adding integer values and saving it in a text(255) data type field, I was able to find a solution for this.

I've converted the integer values first to varchar, pretty much the same data type with caspio's text(255). Just use the code below and update the field name there, to your field name to make this work

SELECT CAST([@field:Category_rel_ID]AS varchar) + " " + CAST([@field:Company_Rel_ID] AS varchar)

This is the screen shot of my DataPage http://prntscr.com/mamqxd , and this is the screen shot of its preview mode http://prntscr.com/mamrmm

You can replace the space with "_" to make the format xxx_xxx(123_321)

I hope this helps.

Regards,

TsiBiRu

[dasigrist]

Great. Thank you for the suggestions.

 

I will try today and let you know what happened.

 

Daniel.

[dasigrist]

Hi,

I tried the formula field and it worked perfectly. This is my formula: Cast([@field:Company_Rel_ID] as varchar) + '_' + Cast([@field:Category_Rel_ID] as varchar)

Thank you so much for your help. 

Daniel

[D3Melb]

On 1/23/2019 at 3:33 AM, dasigrist said:

Hi,

I tried the formula field and it worked perfectly. This is my formula: Cast([@field:Company_Rel_ID] as varchar) + '_' + Cast([@field:Category_Rel_ID] as varchar)

Thank you so much for your help. 

Daniel

Hi, I was just wondering when you got this results, were you expecting a NUMBER you have entered in that CQ_Comp_Cat_Cap table, or the PARAMETER that is connected to another table via RELATIONSHIP?

For example...
--> If Category_Rel_ID is 1 and Company_Rel_ID is 1, while the relationship to one table is that 1=John and another table of 1=Doe... After using the formula, did you get '1(space)1' or 'John(space)Doe'?

[MayMusic]

You can either add a formula field to your table or choose Calculated Value for that combination field on your submission form, use SQL syntax as mentioned by @D3Melb above to get the value in the table.

[D3Melb]

15 hours ago, MayMusic said:

You can either add a formula field to your table or choose Calculated Value for that combination field on your submission form, use SQL syntax as mentioned by @D3Melb above to get the value in the table.

Thank you for the reply May!

[MayMusic]

Of course!

[dasigrist]

Hi @D3Melb, 

After using the formula I get 1_1. and because I want to concatenate NUMBER,STRING,NUMBER, I have to use CAST to convert both numbers to string.