Having trouble getting my Data Store for views to map a data type to an existing

judym598

Certified Senior Developer

over 8 years ago

Having trouble getting my Data Store for views to map a data type to an existing view. I created a view by joining data from 2 tables. I then created a data type to associate with that view so I could use it to display data in a dropdown list (or grid). When I add the data type to the DS for Views - it says the mappings cannot be verified. If I let it 'create' the table - it creates a new table (not the view). I downloaded the XSD from the data type I created and tried to manually map it to the view (as was suggested in another post), then deleted the data type and created it from the new XSD. Still doesn't work when I try to publish the DS.
Here's what I used to create my view:
CREATE ALGORITHM = UNDEFINED SQL SECURITY DEFINER VIEW `ipsproductsubmitter_v` AS SELECT `a`.`id`, concat (`a`.`id`, " - ", `a`.`productname`) AS `IP Name`, `a`.`productdescription`,`b`.`submittername1` AS `Submitter 1`, `b`.`submittername2` AS `Submitter 2` FROM `Appian`...

Discussion posts and replies are publicly visible

0 judym598
Certified Senior Developer
over 8 years ago

....`ipsproduct` `a` JOIN `Appian`.`ipssubmitter` `b` WHERE `a`.`submitterid` = `b`.`
WHERE `a`.`submitterid` = `b`.`id` ORDER BY `IP Name`

Here's the updated XSD from the data type:
<xsd:schema xmlns:xsd="www.w3.org/.../XMLSchema" xmlns:tns="urn:com:appian:types" targetNamespace="urn:com:appian:types">
<xsd:complexType name="IPS_ProductSubmitter_v">
<xsd:annotation>
<xsd:documentation><![CDATA[Data Type associated with the ipsproductsubmitter_v (View)]]></xsd:documentation>
<xsd:appinfo source ="appian.jpa">
                    @Table(name="ipsproductsubmitter_v")
           </xsd:appinfo>
          </xsd:annotation>
           <xsd:sequence>
<xsd:element name="id" nillable="true" type="xsd:int">
<xsd:annotation>
<xsd:appinfo source="appian.jpa">@Id</xsd:appinfo>
</xsd:annotation>
</xsd:element>
<xsd:element name="ipname" nillable="true" type="xsd:string" />
<xsd:element name="productdescription" nillable="true" type="xsd:string" />...
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 judym598
Certified Senior Developer
over 8 years ago

...
<xsd:element name="submitter1" nillable="true" type="xsd:string" />
<xsd:element name="submitter2" nillable="true" type="xsd:string" />
</xsd:sequence>
</xsd:complexType>
</xsd:schema>
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 Chris over 8 years ago

Few things I learned when trying to connect a view to a CDT:

1) View has to contain a unique ID column, populated (looks like you have that under @Id)

2) The unique ID column had to be defined as "bigint" in the XSD, specifically:

<xsd:element name="Id" nillable="true" type="xsd:int">
<xsd:annotation>
<xsd:appinfo source="appian.jpa">
@Id
@Column(columnDefinition="bigint NOT NULL")
</xsd:appinfo>
</xsd:annotation>
</xsd:element>
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 judym598
Certified Senior Developer
over 8 years ago

Just updated my xsd as suggested and also changed my 'ORDER BY' clause in the view to ORDER BY id. This worked! Thank you!
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel
0 Chris over 8 years ago

Great!
Cancel
Vote Up 0 Vote Down

Sign in to reply

Verify Answer

Cancel