How to store a specific non-zero default value in a linked field?

[manishc97360515]

<srcSchema _cs="TestOMPRO4334 (mel)" created="2018-02-28 06:22:51.886Z" createdBy-id="0"

desc="For testing folder mapping issue" entitySchema="xtk:srcSchema" img="xtk:schema.png"

label="TestOMPRO4334" labelSingular="TestOMPRO4334" lastModified="2018-03-01 10:50:33.285Z"

mappingType="sql" md5="59A8BBF33E19ADCEA02AF85B6261C5EF" modifiedBy-id="0"

name="TestOMPRO4334" namespace="mel" xtkschema="xtk:srcSchema">

  <createdBy _cs=""/>

  <modifiedBy _cs=" "/>

  <element autopk="true" desc="For testing folder mapping issue" label="TestOMPRO4334"

labelSingular="TestOMPRO4334" name="TestOMPRO4334">

    <attribute label="First Name" name="firstName" type="string"/>

    <attribute label="Last Name" name="lastName" type="string"/>

    <attribute label="E-Mail" name="email" type="string"/>

    <attribute label="Phone Number" name="phoneNumber" type="string"/>

    <element default="DefaultFolder(‘nmsFolder)" label="Folder" name="folder" revDesc="TestOMPRO4334 included in this folder"

revIntegrity="define" revLabel="TestOMPRO4334s" target="xtk:folder"

type="link"/>

</element>

</srcSchema>

Hi, I am using the above-mentioned schema for some PoC purpose. I am trying to populate data in the above-mentioned schema through a workflow where the data is read from a CSV file and stored in the table corresponding to the above-schema. When a folder id (foreign key to xtk:folder) is provided, then, the system stores it correctly in the iFolderId field of the table, but, when no folder id is provided/chosen, then, 0 (i.e. zero) is stored as a folder ID. My requirement is to define a specific non-zero value as the folder ID. I tried different permutation and combination of  default="DefaultFolder(‘nmsFolder)"  attribute, but, it is not working. Please let me know how to store a non-zero default value in the folder field when folder id is not provided while importing a data set.

[daniell35335226]

Hi Manish,

Could you not do something similiar to this:

    <attribute default="DE" advanced="true" label="Country Code" length="2" name="countryCode"

               notNull="true" required="true" type="string"/>

<element externalJoin="true" label="Country" name="mySchema-country" revIntegrity="normal"

             revLabel="MY Schema" revLink="country-mySchema" target="nms:country"

             type="link">

      <join xpath-dst="@isoA2" xpath-src="@countryCode"/>

    </element>

You have the foreign key folder id inside the table but hidden from immediate view, and you default the value going in?

Dan

[Adhiyan]

Hi Manish,

Try to use sqlDefault="1234" attribute. This will assign 1234 as the folder ID by default.

Regards,
Adhiyan

[manishc97360515]

Hi Dan, thanks! I will try your approach.

[manishc97360515]

Hi Adhiyan, thanks for your reply, but, unfortunately 'sqlDefault' cannot be used with a linked <element>. I tried this and the system is throwing error while saving the schema. Please let me know how to a achieve it using the "default" attribute.

[daniell35335226]

Manish, let me know if the alternative route worked if not I can help further...

[manishc97360515]

Hi Daniel, this is not working because as soon as I use an external join the resulting SQL is setting the default value of the folderID in the source table as 0. If there was a way to use the 'default' (attribute of <element> element) properly, then, it would have been solved.

[manishc97360515]

Hi Daniel, this is not working because as soon as I use an external join the resulting SQL is setting the default value of the folderID in the source table is set as 0. If there was a way to use the 'default' (attribute of <element> element) properly, then, it would have been solved.

[manishc97360515 · Accepted solution]

Solved it. What one has to do is to make changes in schema. Then, go to Tools --> Update Database Structure --> go to next until you find the generated SQL command. Over there the field id (which one wants to set as default to non-zero value) in the SQL query is set as zero. Change the value to your desired value and then, run the query. 

[daniell35335226]

Hi Manish,

Glad you found the solution and thanks for updating for us to see

Kind Regards,

Dan