10-19-2009 08:02 AM
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?
10-21-2009 11:40 AM
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:
<keyReference id="63dbafd9-57f9-4f32-a860-ffe02b7dde27" ordinalPosition="1" />
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.
10-22-2009 06:47 AM
10-22-2009 08:37 AM
John PeronaDirector, Infor CRM Development
10-26-2009 03:53 PM
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?
10-26-2009 04:17 PM
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.
10-26-2009 04:22 PM
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.
10-26-2009 05:04 PM
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.
10-28-2009 07:45 AM
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?