Infoset Codes - Infoset Database View

Infoset Database View

 

Code - enter a code to identify the view

Go Button - click go for the system to default the view from the infoset code setup

View - update / amend the view as required

Save View - save the text of the view in the Sage 300 database

Delete View - click to delete the text of the view from the Sage 300 database

Create View - click to create a database view in the database, based on the view

Drop View - click to drop (delete) the database view from the database

 

Note : the view is limited to 2500 characters - so keep optional field names relatively short

 

For example, setting up EBCUS as an infoset on ARCUSTOMER would result in the following view being created when you click the GO (>>) button

CREATE VIEW EBCUS as SELECT ARCUS.*, [AC].value as [AC], [ARCNTCTTYPE].value as [ARCNTCTTYPE], [CREDTWARNING].value as [CREDTWARNING], [DISCNTGRACE].value as [DISCNTGRACE], [DUEDATEPRD].value as [DUEDATEPRD], [LEASEDATE].value as [LEASEDATE], [LEASEEXPIRY].value as [LEASEEXPIRY], [LEASEPERIOD].value as [LEASEPERIOD], [MAXORDER].value as [MAXORDER], [MINORDER].value as [MINORDER], [NOTE].value as [NOTE], [PREFERCUST].value as [PREFERCUST], [PROSPECT].value as [PROSPECT], [RENTCUSTOMER].value as [RENTCUSTOMER], [SICCODE].value as [SICCODE], [TEMPCUSTOMER].value as [TEMPCUSTOMER] FROM ARCUS left outer join ARCUSO as [AC] on ARCUS.IDCUST = [AC].IDCUST and [AC].optfield = 'AC' left outer join ARCUSO as [ARCNTCTTYPE] on ARCUS.IDCUST = [ARCNTCTTYPE].IDCUST and [ARCNTCTTYPE].optfield = 'ARCNTCTTYPE' left outer join ARCUSO as [CREDTWARNING] on ARCUS.IDCUST = [CREDTWARNING].IDCUST and [CREDTWARNING].optfield = 'CREDTWARNING' left outer join ARCUSO as [DISCNTGRACE] on ARCUS.IDCUST = [DISCNTGRACE].IDCUST and [DISCNTGRACE].optfield = 'DISCNTGRACE' left outer join ARCUSO as [DUEDATEPRD] on ARCUS.IDCUST = [DUEDATEPRD].IDCUST and [DUEDATEPRD].optfield = 'DUEDATEPRD' left outer join ARCUSO as [LEASEDATE] on ARCUS.IDCUST = [LEASEDATE].IDCUST and [LEASEDATE].optfield = 'LEASEDATE' left outer join ARCUSO as [LEASEEXPIRY] on ARCUS.IDCUST = [LEASEEXPIRY].IDCUST and [LEASEEXPIRY].optfield = 'LEASEEXPIRY' left outer join ARCUSO as [LEASEPERIOD] on ARCUS.IDCUST = [LEASEPERIOD].IDCUST and [LEASEPERIOD].optfield = 'LEASEPERIOD' left outer join ARCUSO as [MAXORDER] on ARCUS.IDCUST = [MAXORDER].IDCUST and [MAXORDER].optfield = 'MAXORDER' left outer join ARCUSO as [MINORDER] on ARCUS.IDCUST = [MINORDER].IDCUST and [MINORDER].optfield = 'MINORDER' left outer join ARCUSO as [NOTE] on ARCUS.IDCUST = [NOTE].IDCUST and [NOTE].optfield = 'NOTE' left outer join ARCUSO as [PREFERCUST] on ARCUS.IDCUST = [PREFERCUST].IDCUST and [PREFERCUST].optfield = 'PREFERCUST' left outer join ARCUSO as [PROSPECT] on ARCUS.IDCUST = [PROSPECT].IDCUST and [PROSPECT].optfield = 'PROSPECT' left outer join ARCUSO as [RENTCUSTOMER] on ARCUS.IDCUST = [RENTCUSTOMER].IDCUST and[RENTCUSTOMER].optfield = 'RENTCUSTOMER' left outer join ARCUSO as [SICCODE] on ARCUS.IDCUST = [SICCODE].IDCUST and [SICCODE].optfield = 'SICCODE' left outer join ARCUSO as [TEMPCUSTOMER] on ARCUS.IDCUST = [TEMPCUSTOMER].IDCUST and [TEMPCUSTOMER].optfield = 'TEMPCUSTOMER'

 

Which would appear in Crystal as a single table :

 

 

With the following fields. IE there is no need to use subreports / joins etc to access optional fields