SQL Views in 7.5.2

[DanCarvin]

A customer is interested in converting it's LAN SLX to SLX Web.   In their LAN SLX, there are a few detail view fields and tab datagrids that display summerized data from SQL Views.   So far, SQL Views in SLX Web have been problematic.   Is this improved in 7.5.2?

[TSturr]

Anybody have an answer to this?  We are converting to web client (7.5.2) and this hole is an unexpected pain event for us.  Are there work arounds?

Ted

[ToddHardin]

Have had some success using Views in v7.5.1 AA/Web. There a couple of manual tasks needed to workaround some missing and/or incorrect meta data.  Note - these manual steps *might* not be necessary in SP2, however I have not confirmed that.

I believe the steps are:

Enable the view using DB Manager (Legacy Architect or SLX Administrator application)

Manually edit table RESYNCTABLEDEFS to provide a KEYFIELD value for the Table/View record.

Use AA to "Create a business entity from an existing table"

Edit the MyEntityName.MYENTITYNAME.entity.xml file to set the correct GUID for the KeyReference Id

Using either the VFS Explorer (if your Project is stored in the VFS) or Windows File Explorer (if your project is store on Local File System/LFS) you can find this file in the following folder:

    \Model\Entity Model\SalesLogix Application Entities\MyEntityName

(note - if working in a Project stored in LFS, to be safe I recommend that you exit out of AA application)

Look for the <keyproperties> element, for example:

  <keyProperties>
    <keyReference id="63dbafd9-57f9-4f32-a860-ffe02b7dde27" ordinalPosition="1" />
  </keyProperties>

Change the GUID to match the GUID for the "OrmFieldProperty" id value of the Property that is effectively a PKID for your view. That GUID will typically be found a bit lower in the XML file.

[garnold]

This might be helpful. Lane Zabriskie from Light Industries gave me a pretty cool example he built a while back. What is does is uses views to grab the Northwind database then ties it into SLX web via the entity model. I found this the best way of getting outside data into SLX web because using the entity model now allows you to use all the other cool stuff our web product has to offer. Great example Lane!

[jperona]

As others have stated there are workarounds in 7.5.1.  In 7.5.2, we have modified the code in DB Manager to set the appropriate fields.  The one item to remember is that in order for the View to be imported into the Web Entity Model it must have a Primary key associated to the view.

[DanCarvin]

Let me share the results of my attempt...   I created a SQL view with Accountid as the primary key and enabled it in Administrator.  For the LAN that's all you need to do.   In the Application Architect (I believe it was 7.5.0 at the time), I was able to create an entity based on the view and create an "extension table" relationship to account through the accountid.   For existing accounts, it even displayed the correct summarized data.

But when I tried to add a new account record, the operation failed due to an error trying to add a record to my custom entity which was actually a view (Duh!).  I looked for a way to make it not try to add a record while keeping linked & easy to access for display, but didn't find one.

So is there another way to do this?

[TSturr]

We actually have this working in our system now (thanks to Todd's posting).  I don't know why you used AccountID in your view.  If you follow what Todd said:  Manually edit table RESYNCTABLEDEFS to provide a KEYFIELD value for the Table/View record. You should be able to use the view without any issues. 

One caveat that had us stuck for a while, make sure you insert the keyfield as UPPER CASE, it doesn't like lower case or proper case - remember this stuff is all case sensitive.

Ted

[ToddHardin]

Great to hear Ted.  Say, in your scenario was your view configured as a 1:1 extension entity as well, or was your view more of a 1:M Child table?

Just wondering if Dan is running into a situation that is unique to 1:1 extension entities.

Todd Hardin

[TSturr]

Our scenario is different from what Dan posted because we are using foreign keys to our views.  In one case we use the Account.externalaccountno field to link to our ERP customer record.  So in that case it is a 1:1 relationship, but we set the keyfield to be CUSTOMER_ID in the resynctabledefs table.  He was using the AccountID field which I think was causing him to have the result where it wanted to automatically add a record into is view record.

Ted

[DanCarvin]

In my scenario the customer wanted to see an account's total sales over several years, and to make it available so users comfortable with query builder could display the figure or base criteria on it (TotalSales > 100,000).   So it makes sense to create a view that returns a accountids with aggregate sums of their closed sales.  

This works great in SLX LAN, but can it be extended to the Web so it doesn't try to create a record on insert?   Maybe if it was configured for 1:M even though it isn't?