Records / Datastores / Tables

Greetings All,

I am new to Appian.  First I would like to thank Appian for creating the Community Edition.  Fabulous!!

I have been through various courses relating to records and database concepts.

I have experience with database design and I used the database tools to create three tables in the Appian schema with the necessary columns, primary keys and foreign key relationships.  I also manually populated some rows of data into these tables.

I created a new application.  I think I now have to create Records that refer or point to these physical tables?  Or do I need to create Datastores and then Records that refer to the Datastores?

I am happy to take a course that discusses these concepts, but I am having trouble finding information that ties the physical tables to objects that I would use in process models or with interfaces.

Thanks very much for any pointers!!

Paul

  Discussion posts and replies are publicly visible

  • Welcome to the community! I think the best approach is to create both record types and data stores to point to your tables. The easiest way to think about these is how you use them:

    • The Record Type is used to display data. You can use the record type to show the results in a grid or a view into a single record item.
    • The Data Store is used to update data. So if you plan to make inserts / updates from Appian, you'll need a data store to connect to your tables.

    Once you create these objects, you use them as a proxy for using data from the table. For example, if you'd like to insert a new row into a table, you say that the source you want to write to is your data store entity; for the opposite, if you'd like to create a grid that shows your records, you use the record type as the source for the grid component. This allows a layer of separation between the table itself and how you use it in Appian.

  • Thanks Peter!!

    And that makes sense to me.  But when I try to create a record or a datastore, I do not see the table that I created in the database designer as shown below.

    I tried for both Records and Data Store.

    I am sure I am missing the point somewhere with either the approach or the security or something!!

    Thanks again for your help!!

    Paul

    My table structure here:

    Creating a Record (or not)...

    I do not see my table here...

    So then try to create a Data Store:

    Still cannot find anything that looks like my table.

  • Ah I should have mentioned one more thing - you will also need a custom data type (CDT)! The custom data type defines the structure of your table so that Appian can allow you to easily use objects with the same structure as variables. For instance, if you want a form that inserts data about your customer, a variable can be created with your customer CDT so that it contains the same fields as exist in the database.

    Try creating a custom data type (it's just called Data Type in the designer) and you should see an option to "Create from database table or view". Once you do this, hopefully should see your table. Then you can actually create your data store directly from the same screen if you'd like.

  • Thanks to both of you.  That worked just fine (with one small exception).  One of my tables was defined with three columns as Geospatial datatype as 'Point'.  These were excluded as unsupported when creating the CDT.  I searched 'geospatial' in the documentation but there was no results.  Also, am I using MySQL, InnoDB (or both)?  I thought I saw somewhere that I might have to use a BLOB datatype.

    Thanks all for your help!!

    Best,

    Paul

  • 0
    Certified Lead Developer
    in reply to paulp0005

    AFAIK Appian does not support BLOBs nor geospatial. Would it work turning that into a string?

  • Thanks Stefan!!  I am a bit unfamiliar with the point datatype as I have only had one geospatial project using SQL Server and related queries in the past (e.g., is this point in a zone (polygon)).  I am not losing sleep over this one yet.  I will get by on lat/long as a decimal as, oddly, it seems to have more precision than a float or double.