Hi Community,
I am using Microsoft SQL Server for my Application Application. In my CDT (Say Employee) I have an array field (Say Skills) to store checkbox values but the problem is that when i go to data store to verify it and publish. It asks for Employee_Skills table. if i define it as EmployeeSkills then it throws an error saying table not found. Also by default it generates weird column names in the new table. How can i control naming convention of my foreign key table and choose friendly column name ?
I checked everywhere in CDT's XSD file also but i couldn't find any way to change table/column name.
Discussion posts and replies are publicly visible
prais1852 , The below snippet would help you. Please modify accordingly in your .xsd file.
<xsd:complexType name="Emplyee"> <xsd:annotation> <xsd:appinfo source="appian.jpa"> @Table(name="Emplyee_dbtable") </xsd:appinfo> </xsd:annotation> <xsd:sequence> ---------------- ---------------- ---------------------
Hi and Carlos Santander Here is my CDT and XSD. It asks for CCCDetails_NodesAffected and CCCDetails_ToolsAffected tables when i try to publish my CDT to Data Store. How can i overwrote table and column names ? I can't find CCCDetails_NodesAffected and CCCDetails_ToolsAffected table names in my CDT. Please help me to resolve this. I want name to be without underscore i.e. CCCDetailsNodesAffected and CCCDetailsToolsAffected and similarly i want to overwrite the column names as well. I have two fields as Array and i do not want to create Nested CDTs.
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:tns="urn:com:appian:types:CCCD" targetNamespace="urn:com:appian:types:CCCD"> <xsd:complexType name="CCCDetails"> <xsd:annotation> <xsd:documentation><![CDATA[Information about CCC in the CCC Application]]></xsd:documentation> </xsd:annotation> <xsd:sequence> <xsd:element name="CCCNumber" nillable="true" type="xsd:int"> <xsd:annotation> <xsd:appinfo source="appian.jpa">@Id @GeneratedValue</xsd:appinfo> </xsd:annotation> </xsd:element> <xsd:element name="Approved" nillable="true" type="xsd:string"> <xsd:annotation> <xsd:appinfo source="appian.jpa">@Column(length=255)</xsd:appinfo> </xsd:annotation> </xsd:element> <xsd:element name="ApprovalDate" nillable="true" type="xsd:date" /> <xsd:element name="PRNumber" nillable="true" type="xsd:string"> <xsd:annotation> <xsd:appinfo source="appian.jpa">@Column(length=255)</xsd:appinfo> </xsd:annotation> </xsd:element> <xsd:element name="ECR" nillable="true" type="xsd:string"> <xsd:annotation> <xsd:appinfo source="appian.jpa">@Column(length=255)</xsd:appinfo> </xsd:annotation> </xsd:element> <xsd:element name="Description" nillable="true" type="xsd:string"> <xsd:annotation> <xsd:appinfo source="appian.jpa">@Column(length=200)</xsd:appinfo> </xsd:annotation> </xsd:element> <xsd:element name="Class" nillable="true" type="xsd:string"> <xsd:annotation> <xsd:appinfo source="appian.jpa">@Column(length=5)</xsd:appinfo> </xsd:annotation> </xsd:element> <xsd:element name="Reclass" nillable="true" type="xsd:string"> <xsd:annotation> <xsd:appinfo source="appian.jpa">@Column(length=5)</xsd:appinfo> </xsd:annotation> </xsd:element> <xsd:element name="SixMonthsMaterialRequired" nillable="true" type="xsd:string"> <xsd:annotation> <xsd:appinfo source="appian.jpa">@Column(length=5)</xsd:appinfo> </xsd:annotation> </xsd:element> <xsd:element name="ChangeType" nillable="true" type="xsd:string"> <xsd:annotation> <xsd:appinfo source="appian.jpa">@Column(length=255)</xsd:appinfo> </xsd:annotation> </xsd:element> <xsd:element name="NotificationDate" nillable="true" type="xsd:date" /> <xsd:element name="CCCPresenter" nillable="true" type="xsd:string"> <xsd:annotation> <xsd:appinfo source="appian.jpa">@Column(length=255)</xsd:appinfo> </xsd:annotation> </xsd:element> <xsd:element name="InEccos" nillable="true" type="xsd:string"> <xsd:annotation> <xsd:appinfo source="appian.jpa">@Column(length=5)</xsd:appinfo> </xsd:annotation> </xsd:element> <xsd:element name="DateAddedToEccos" nillable="true" type="xsd:date" /> <xsd:element name="CEOldBomPN" nillable="true" type="xsd:string" /> <xsd:element name="OldRevision" nillable="true" type="xsd:string" /> <xsd:element name="CENewBomPN" nillable="true" type="xsd:string" /> <xsd:element name="NewRevision" nillable="true" type="xsd:string" /> <xsd:element name="ProductGroupAffected" nillable="true" type="xsd:string"> <xsd:annotation> <xsd:appinfo source="appian.jpa">@Column(length=255)</xsd:appinfo> </xsd:annotation> </xsd:element> <xsd:element maxOccurs="unbounded" minOccurs="0" name="ToolsAffected" nillable="true" type="xsd:string" /> <xsd:element maxOccurs="unbounded" minOccurs="0" name="NodesAffected" nillable="true" type="xsd:string" /> <xsd:element name="ApprovedBy" nillable="true" type="xsd:string"> <xsd:annotation> <xsd:appinfo source="appian.jpa">@Column(length=100)</xsd:appinfo> </xsd:annotation> </xsd:element> <xsd:element name="Spared" nillable="true" type="xsd:string"> <xsd:annotation> <xsd:appinfo source="appian.jpa">@Column(length=5)</xsd:appinfo> </xsd:annotation> </xsd:element> <xsd:element name="Sbom" nillable="true" type="xsd:string"> <xsd:annotation> <xsd:appinfo source="appian.jpa">@Column(length=5)</xsd:appinfo> </xsd:annotation> </xsd:element> <xsd:element name="BetaRequired" nillable="true" type="xsd:string"> <xsd:annotation> <xsd:appinfo source="appian.jpa">@Column(length=5)</xsd:appinfo> </xsd:annotation> </xsd:element> <xsd:element name="BetaRequested" nillable="true" type="xsd:string"> <xsd:annotation> <xsd:appinfo source="appian.jpa">@Column(length=5)</xsd:appinfo> </xsd:annotation> </xsd:element> <xsd:element name="Bundled" nillable="true" type="xsd:string"> <xsd:annotation> <xsd:appinfo source="appian.jpa">@Column(length=5)</xsd:appinfo> </xsd:annotation> </xsd:element> <xsd:element name="BundleQtr" nillable="true" type="xsd:int" /> <xsd:element name="BundlePresentDate" nillable="true" type="xsd:date" /> <xsd:element name="QualStatus" nillable="true" type="xsd:string"> <xsd:annotation> <xsd:appinfo source="appian.jpa">@Column(length=255)</xsd:appinfo> </xsd:annotation> </xsd:element> <xsd:element name="QualPartsDate" nillable="true" type="xsd:date" /> <xsd:element name="QualApprovalDate" nillable="true" type="xsd:date" /> <xsd:element name="Comments" nillable="true" type="xsd:string" /> <xsd:element name="CreatedBy" nillable="true" type="xsd:string"> <xsd:annotation> <xsd:appinfo source="appian.jpa">@Column(length=100)</xsd:appinfo> </xsd:annotation> </xsd:element> <xsd:element name="CreatedOn" nillable="true" type="xsd:dateTime" /> <xsd:element name="UpdatedBy" nillable="true" type="xsd:string"> <xsd:annotation> <xsd:appinfo source="appian.jpa">@Column(length=100)</xsd:appinfo> </xsd:annotation> </xsd:element> <xsd:element name="UpdatedOn" nillable="true" type="xsd:dateTime" /> </xsd:sequence> </xsd:complexType> </xsd:schema>
Hi and Carlos Santander I am still looking for help. Can you help with above mentioned problem ?
prais1852 , Please check the attached .xsd for reference. This would help you.
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:tns="urn:com:appian:types" targetNamespace="urn:com:appian:types"> <xsd:complexType name="EMPLOYEE_TYPE"> <xsd:annotation> <xsd:appinfo source="appian.jpa"> @Table(name="EMPLOYEE") </xsd:appinfo> </xsd:annotation> <xsd:sequence> <xsd:element name="Id" nillable="true" type="xsd:int"> <xsd:annotation> <xsd:appinfo source="appian.jpa">@Column(name="ID") @Id @GeneratedValue</xsd:appinfo> </xsd:annotation> </xsd:element> <xsd:element name="empId" nillable="true" type="xsd:int"> <xsd:annotation> <xsd:appinfo source="appian.jpa">@Column(name="EMPID")</xsd:appinfo> </xsd:annotation> </xsd:element> <xsd:element name="dateTime" nillable="true" type="xsd:dateTime"> <xsd:annotation> <xsd:appinfo source="appian.jpa">@Column(name="DATE_TIME")</xsd:appinfo> </xsd:annotation> </xsd:element> <xsd:element name="createdUser" nillable="true" type="xsd:string"> <xsd:annotation> <xsd:appinfo source="appian.jpa">@Column(name="CREATED_USER", length=50)</xsd:appinfo> </xsd:annotation> </xsd:element> <xsd:element name="createDate" nillable="true" type="xsd:dateTime"> <xsd:annotation> <xsd:appinfo source="appian.jpa">@Column(name="CREATED_DATE")</xsd:appinfo> </xsd:annotation> </xsd:element> <xsd:element name="modifiedUser" nillable="true" type="xsd:string"> <xsd:annotation> <xsd:appinfo source="appian.jpa">@Column(name="MODIFIED_USER", length=50)</xsd:appinfo> </xsd:annotation> </xsd:element> <xsd:element name="modifiedDate" nillable="true" type="xsd:dateTime"> <xsd:annotation> <xsd:appinfo source="appian.jpa">@Column(name="MODIFIED_DATE")</xsd:appinfo> </xsd:annotation> </xsd:element> <xsd:element name="isActive" nillable="true" type="xsd:boolean"> <xsd:annotation> <xsd:appinfo source="appian.jpa">@Column(name="IS_ACTIVE")</xsd:appinfo> </xsd:annotation> </xsd:element> <xsd:element name="isPosted" nillable="true" type="xsd:boolean"> <xsd:annotation> <xsd:appinfo source="appian.jpa">@Column(name="IS_POSTED")</xsd:appinfo> </xsd:annotation> </xsd:element> </xsd:sequence> </xsd:complexType> </xsd:schema>
Thanks for the example. But if you can see the screenshot and XSD i have attached. There are two fields ToolsAffected and NodesAffected which are array in my Designer CDT and when i go to publish this CDT Appian automatically asks to create two more table. In XSD i can't see the table/column name which appian suggests. How can i overwrite the name in that case ?
I can see only -
<xsd:element maxOccurs="unbounded" minOccurs="0" name="ToolsAffected" nillable="true" type="xsd:string" /> <xsd:element maxOccurs="unbounded" minOccurs="0" name="NodesAffected" nillable="true" type="xsd:string" />
and Appian is creating other two definition table internally. How can i find them to edit the table and column names ?
Hi and nageswararaoa Can you please help me with this ?
Hi I have two fields as checkbox on my form (ToolsAffected and NodesAffected). To save those values i have used them to be as array values insted of going into nested CDT while designing my CDT. Here is the screenshot of my CDT that i designed for that purpose -
I didn't create any tables in my MS SQL Server before publishing the data type to data store. I used the DDL script generated by Appian itself. Here is the query -
create table [cccdetails_nodesaffected] ( [cccdtils_ndsffcted_cccnmber] int not null, elt nvarchar(255) null, [cccdetails_ndesaffected_idx] int not null, primary key ([cccdtils_ndsffcted_cccnmber], [cccdetails_ndesaffected_idx]) ); create table [cccdetails_toolsaffected] ( [cccdtils_tlsffcted_cccnmber] int not null, elt nvarchar(255) null, [cccdetails_tolsaffected_idx] int not null, primary key ([cccdtils_tlsffcted_cccnmber], [cccdetails_tolsaffected_idx]) ); create table [cccdetails] ( [cccnumber] int identity not null, [approved] nvarchar(255) null, [approvaldate] datetime null, [prnumber] nvarchar(255) null, [ecr] nvarchar(255) null, [description] nvarchar(200) null, [class] nvarchar(5) null, [reclass] nvarchar(5) null, [sixmonthsmaterialrequired] nvarchar(5) null, [changetype] nvarchar(255) null, [notificationdate] datetime null, [cccpresenter] nvarchar(255) null, [ineccos] nvarchar(5) null, [dateaddedtoeccos] datetime null, [ceoldbompn] nvarchar(255) null, [oldrevision] nvarchar(255) null, [cenewbompn] nvarchar(255) null, [newrevision] nvarchar(255) null, [productgroupaffected] nvarchar(255) null, [approvedby] nvarchar(100) null, [spared] nvarchar(5) null, [sbom] nvarchar(5) null, [betarequired] nvarchar(5) null, [betarequested] nvarchar(5) null, [bundled] nvarchar(5) null, [bundleqtr] int null, [bundlepresentdate] datetime null, [qualstatus] nvarchar(255) null, [qualpartsdate] datetime null, [qualapprovaldate] datetime null, [comments] nvarchar(255) null, [createdby] nvarchar(100) null, [createdon] datetime null, [updatedby] nvarchar(100) null, [updatedon] datetime null, primary key ([cccnumber]) ); alter table [cccdetails_nodesaffected] add constraint FKAFE31CC5F1D89349 foreign key ([cccdtils_ndsffcted_cccnmber]) references [cccdetails]; alter table [cccdetails_toolsaffected] add constraint FK5ACB09EF1292D00B foreign key ([cccdtils_tlsffcted_cccnmber]) references [cccdetails];
When i checked in the CDT, It is -
I want to rename the CCCDetails_ToolsAffected and CCCDetals_NodesAffected table name as well as column names without changing PK/FKs. But i couldn't find these names in XSD. Can you please help me in fixing it ?
My personal preference is not to use Appian to generate the tables. I'd suggest you read the documentation as there's a lot of good information regarding database schema best practices amongst other things.
If you're happy with badly-named items in the database - such as constraints named 'FK5ACB09EF1292D00B' - and still wish to continue using Appian to generate the tables, you'll probably be better off dropping the tables that were created, changing the XSDs and then republishing the XSD and using the DDL to recreate the tables.