Hi

Trying to connect to sage1000 and show data via lookup in web slx 7.5.4

i created view then enabled via db manager, then created entitiy, then created lookup to view on screen builds and runs but no data brought through?

tried to do grid on lan - to check and this data not brought through either but no error message?

when run sql the statements bring back data.

thought one prob might be union so did created another view to look at this view and used that as entity but still same problem.

any suggestions would be greatly appreciated

thanks

Chez

CREATE VIEW [sysdba].[DCLProductsServices]

ProductGroupC FROM [TD-S1000V3].[demo].[scheme].InventoryControl_Product

UNION

ProductGroupC FROM [TD-S1000V3].[demo].[scheme].SalesOrder_Service

CREATE VIEW [sysdba].

[DCLSalesOrderOrderHeader]

AS

SELECT AddressLine1, AddressLine2, AddressLine3, AddressLine4, AddressLine5, AlphaCode, Appearance, CarrierCode, IndustrialClass, OrderCostBase, DiscountCategory,CustomerCode, CustomerOrderNumber, DateDespatched, DateEntered, DatePromised, DateReceived, DateRequired, DeliveryMethod, DeliveryReason, DiscountBase,EnteredBy, FromIndicator, FromIndicator_Label, FromReference, GrossBase, GrossValue, OrderHeld, OrderHeld_Label, InvoiceCustomer, NettBase, NettValue,OrderCost, DiscountAmount, OrderNumber, PickingList, PostCode, PriceList, DateForPricing, Region, ShipmentResponsibility, StatementCustomer, OrderStatus,OrderStatus_Label, Territory, AnalysisCodeA, AnalysisCodeB, AnalysisCodeC,VATType FROM [TD-S1000V3].[demo].[scheme].SalesOrder_OrderHeader AS SalesOrderOrderHeader WITH (READUNCOMMITTED) WHERE (OrderStatus IN ('1', '2', '3', '4', '5', '6', '7'))

On the Web you have an Entity created for this? Dpployed to sData, SLXClient, et al.

on the LAN you created a table for this in SLX and then deleted the table in SQL and replaced it with a SQL view?

when created view can see in db manager so in lan just created a form with datagrid to view data, pointing to view but nothing appears.

yes, have created entity in web and deployed to slxclient as only using this part.

When you run the SQL Statement and/or the View from SQL MAnagement Studio (assuming you are running MS SQL), are you logged in as sysdba?

I see your Statement to Create the View does uses the sysdba schema, this means that SLX can see the view, but that doesn't necessarily mean that SalesLogix can retrieve the data.

In other words, make sure that you test the View from the SQL level while logged in as sysdba. Once you are able to succesfully query it, then you should be able to go into SalesLogix and use it as you want it.

yes, logged into sql query as sysdba and tried running it and it works fine.

went to slx and run datagrid and lookup in lan and nothing - no data still..

how else can i load the data into the lookup on web, is it possible to do via nhibernate query and attach it to lookup somehow?

this is just soooo frustrating now!!!

found that if did coded dgrig.sql.text and dg refreshed on open of form in the lan, then it show the values.

can web lookup be coded via on load event?

Quote: Originally posted by chez found that if did coded dgrig.sql.text and dg refreshed on open of form in the lan, then it show the values. can web lookup be coded via on load event?

I was expecting you were already doing this.

If the LAN Client is loading the Data, I don't see why the Web Client wouldn't load it.

Here is what I would do:

On the SLX Web Server, run SalesLogix Profiler while loading your entity. This will show you the Actual SQL Statement being generated and the one being executed.

on the web only have a lookup on the returns screen, ran sql profiler and nothing as don't think lookups show up or if they do, mine didn't!! ( the datagrid was only in lan to check values there.)

You know that Ryan posted an article up here a few years ago on how to do this.....

1. Create the table in SalesLogix Database Manager.....

If you have another step 1 in mind (like CREATE VIEW) then you are geschrooooot.

i.e Full Stop, end of story, period. Your mileage will not vary.

yes...

manage to sort thanks...

created view BUT had to create primarykey value too as seperate column i.e. createview as select ordernumber as PrimaryKey, ordernumber, nettbase...etc from opheadm

then enable via dbmanger and put primaryKey as primarykey

create entity in web

then when place values in datagrid or lookup you use the column 'ordernumber' rather than primarykey column

the values then appear... not sure why primarykey data not show, but at least working now..!!