Jump to content
  • 0

Concatenate 2 fields compose a third one.


Go to solution Solved by AnchovyXL,

Question

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.

caspio_001.png

Link to post
Share on other sites

14 answers to this question

Recommended Posts

  • 1
  • Solution

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!

Link to post
Share on other sites
  • 0

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

Link to post
Share on other sites
  • 0

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]

Link to post
Share on other sites
  • 0
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

2019-01-22_0946.png

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

Link to post
Share on other sites
  • 0
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

image.thumb.png.645f7ddce2bc9785710be6fe29970b38.png

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

Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
×
×
  • Create New...