Formatting in Grid

[SBarnes]

Hi Guys

I have a form that inherits from JiwaFinancials.Jiwa.JiwaApplication.NavigationListUI.NavigationList and uses a stored procedure to fill the grid using the first results set as the schema and the second as the data and it works perfectly including multiple drill downs etc.

But I have one small issue namely formatting numeric values, how do I get a qty to show with a minus instead of brackets and how do I get a currency value to do a similar thing?

Everything I've tried in the stored procedure to set the format string gets ignore when the form appears below is the stored procedure.

Code: Select all

ALTER PROCEDURE [dbo].[usp_JIWA_NavigationList_SalesForGraphedMonthForms] (@StartDate As datetime, @EndDate as datetime, @InventoryID as varchar(20))
AS
SET NOCOUNT ON
   
DECLARE @Schema TABLE
(
   id INT IDENTITY NOT NULL,
   Name VARCHAR(50) NOT NULL,   
   Caption VARCHAR(50) NOT NULL,
   IsVisible BIT NOT NULL,
   DefaultWidth INT NOT NULL,
   IsDrillDown BIT NOT NULL,
   DrillDownSourceIDColumnName VARCHAR(50) NULL,
   DrillDownClassNameColumnName VARCHAR(50) NULL,
   FormatString VARCHAR(255) NULL,
   DecimalPlacesColumnName VARCHAR(50) NULL
)

   -- First Result defines the schema
INSERT INTO @Schema(Name, Caption, IsVisible, DefaultWidth, IsDrillDown, DrillDownSourceIDColumnName, DrillDownClassNameColumnName, FormatString, DecimalPlacesColumnName)
   SELECT 'InvoiceID', 'InvoiceID', 0, 0, 0, NULL, NULL, NULL, NULL
   
   
   
   INSERT INTO @Schema(Name, Caption, IsVisible, DefaultWidth, IsDrillDown, DrillDownSourceIDColumnName, DrillDownClassNameColumnName, FormatString, DecimalPlacesColumnName)
   SELECT 'InvoiceNo', 'InvoiceNo', 0, 0, 0, NULL, NULL, NULL, NULL
   
   
   
   INSERT INTO @Schema(Name, Caption, IsVisible, DefaultWidth, IsDrillDown, DrillDownSourceIDColumnName, DrillDownClassNameColumnName, FormatString, DecimalPlacesColumnName)
   SELECT 'InvHistoryNo', 'InvHistoryNo', 0, 0, 0, NULL, NULL, NULL, NULL   
   
   
   INSERT INTO @Schema(Name, Caption, IsVisible, DefaultWidth, IsDrillDown, DrillDownSourceIDColumnName, DrillDownClassNameColumnName, FormatString, DecimalPlacesColumnName)
   SELECT 'FullInvNo', 'Invoice', 1, 7, 1, 'InvoiceID', 'SY_Forms_ClassNameOrd', NULL, NULL
   
   INSERT INTO @Schema(Name, Caption, IsVisible, DefaultWidth, IsDrillDown, DrillDownSourceIDColumnName, DrillDownClassNameColumnName, FormatString, DecimalPlacesColumnName)
   SELECT 'InvDate', 'Date', 1, 8, 0, 0, NULL, 'dd/MM/yyyy', NULL

   INSERT INTO @Schema(Name, Caption, IsVisible, DefaultWidth, IsDrillDown, DrillDownSourceIDColumnName, DrillDownClassNameColumnName, FormatString, DecimalPlacesColumnName)
   SELECT 'DebtorID', 'DebtorID', 0, 0, 0, NULL, NULL, NULL, NULL

   INSERT INTO @Schema(Name, Caption, IsVisible, DefaultWidth, IsDrillDown, DrillDownSourceIDColumnName, DrillDownClassNameColumnName, FormatString, DecimalPlacesColumnName)
   SELECT 'AccountNo', 'Account', 1, 7, 1, 'DebtorID', 'SY_Forms_ClassNameDB', NULL, NULL

   INSERT INTO @Schema(Name, Caption, IsVisible, DefaultWidth, IsDrillDown, DrillDownSourceIDColumnName, DrillDownClassNameColumnName, FormatString, DecimalPlacesColumnName)
   SELECT 'Name', 'Name', 1, 25, 1, 'DebtorID', 'SY_Forms_ClassNameDB', NULL, NULL

   INSERT INTO @Schema(Name, Caption, IsVisible, DefaultWidth, IsDrillDown, DrillDownSourceIDColumnName, DrillDownClassNameColumnName, FormatString, DecimalPlacesColumnName)
   SELECT 'InventoryID', 'InventoryID', 0, 0, 0, NULL, NULL, NULL, NULL   

   INSERT INTO @Schema(Name, Caption, IsVisible, DefaultWidth, IsDrillDown, DrillDownSourceIDColumnName, DrillDownClassNameColumnName, FormatString, DecimalPlacesColumnName)
   SELECT 'PartNo', 'Part', 1, 5, 1, 'InventoryID', 'SY_Forms_ClassNameInv', NULL, NULL
   
   INSERT INTO @Schema(Name, Caption, IsVisible, DefaultWidth, IsDrillDown, DrillDownSourceIDColumnName, DrillDownClassNameColumnName, FormatString, DecimalPlacesColumnName)
   SELECT 'Description', 'Description', 1, 30, 1, 'InventoryID', 'SY_Forms_ClassNameInv', NULL, NULL
   
   INSERT INTO @Schema(Name, Caption, IsVisible, DefaultWidth, IsDrillDown, DrillDownSourceIDColumnName, DrillDownClassNameColumnName, FormatString, DecimalPlacesColumnName)
   SELECT 'QuantityThisDel', 'Quantity', 1, 7, 0, NULL, NULL, '+#,##0;-#,##0', 'DecimalPlacesQty'
   
   INSERT INTO @Schema(Name, Caption, IsVisible, DefaultWidth, IsDrillDown, DrillDownSourceIDColumnName, DrillDownClassNameColumnName, FormatString, DecimalPlacesColumnName)
   SELECT 'CurrentLineTotal', 'CurrentLineTotal', 0, 0, 0, NULL, NULL, NULL, NULL   
   
   INSERT INTO @Schema(Name, Caption, IsVisible, DefaultWidth, IsDrillDown, DrillDownSourceIDColumnName, DrillDownClassNameColumnName, FormatString, DecimalPlacesColumnName)
   SELECT 'TaxToCharge', 'TaxToCharge', 0, 0, 0, NULL, NULL, NULL, NULL      
   
   INSERT INTO @Schema(Name, Caption, IsVisible, DefaultWidth, IsDrillDown, DrillDownSourceIDColumnName, DrillDownClassNameColumnName, FormatString, DecimalPlacesColumnName)
   SELECT 'NetLinetotal', 'Total', 1, 7, 0, NULL, NULL, '{0:$#,#.00;$-#,#.00}', 'DecimalPlacesTotal'   
   
   INSERT INTO @Schema(Name, Caption, IsVisible, DefaultWidth, IsDrillDown, DrillDownSourceIDColumnName, DrillDownClassNameColumnName, FormatString, DecimalPlacesColumnName)
   SELECT 'SY_Forms_ClassNameOrd', 'SY_Forms_ClassNameOrd', 0, 0, 0, NULL, NULL, NULL, NULL
   
   INSERT INTO @Schema(Name, Caption, IsVisible, DefaultWidth, IsDrillDown, DrillDownSourceIDColumnName, DrillDownClassNameColumnName, FormatString, DecimalPlacesColumnName)
   SELECT 'SY_Forms_ClassNameDB', 'SY_Forms_ClassNameDB', 0, 0, 0, NULL, NULL, NULL, NULL
   
   INSERT INTO @Schema(Name, Caption, IsVisible, DefaultWidth, IsDrillDown, DrillDownSourceIDColumnName, DrillDownClassNameColumnName, FormatString, DecimalPlacesColumnName)
   SELECT 'SY_Forms_ClassNameInv', 'SY_Forms_ClassNameInv', 0, 0, 0, NULL, NULL, NULL, NULL
   
   INSERT INTO @Schema(Name, Caption, IsVisible, DefaultWidth, IsDrillDown, DrillDownSourceIDColumnName, DrillDownClassNameColumnName, FormatString, DecimalPlacesColumnName)
   SELECT 'DecimalPlacesQty', 'DecimalPlacesQty', 0, 0, 0, NULL, NULL, NULL, NULL   
   
   INSERT INTO @Schema(Name, Caption, IsVisible, DefaultWidth, IsDrillDown, DrillDownSourceIDColumnName, DrillDownClassNameColumnName, FormatString, DecimalPlacesColumnName)
   SELECT 'DecimalPlacesTotal', 'DecimalPlacesTotal', 0, 0, 0, NULL, NULL, NULL, NULL   
   

   SELECT Name, Caption, IsVisible, DefaultWidth, IsDrillDown, DrillDownSourceIDColumnName, DrillDownClassNameColumnName, FormatString, DecimalPlacesColumnName
   FROM @Schema
   ORDER BY id

   -- Second Result returns the data
SELECT InvoiceID, InvoiceNo, InvHistoryNo, InvoiceNo + '-D' + RIGHT('00' + CAST(InvHistoryNo AS varchar(2)), 2) AS FullInvNo, InvDate, DebtorID, AccountNo, Name, InventoryID, PartNo, Description, QuantityThisDel, 
                         CurrentLineTotal, TaxToCharge, CurrentLineTotal - TaxToCharge AS NetLinetotal, 'JiwaFinancials.Jiwa.JiwaSalesUI.SalesOrder.SalesOrderEntryForm'  as [SY_Forms_ClassNameOrd],
                  'JiwaFinancials.Jiwa.JiwaDebtorsUI.frmDebtor' as [SY_Forms_ClassNameDB], 'JiwaFinancials.Jiwa.JiwaInventoryUI.InventoryMaintenanceForm' as [SY_Forms_ClassNameInv],
                  0 as [DecimalPlacesQty], 2 as [DecimalPlacesTotal]
FROM SO_VInv
where InvDate >= @StartDate and InvDate <= @EndDate and InventoryID =  @InventoryID
order by InvDate desc


[SBarnes]

I tried going around the problem with the following plugin code but that hasn't fixed it either, interestingly though the commented out foreground colour line does work though, just not the rest of it.

Code: Select all

public override void Start()
      {
         base.Start();
         //FarPoint.Win.Spread.Column NetLinetotal = this.JiwaGrid1.ActiveSheet.Columns["NetLinetotal"];
         
         JiwaFinancials.Jiwa.JiwaApplication.JiwaManageGrid.JiwaCurrencyCellType currcell = new JiwaFinancials.Jiwa.JiwaApplication.JiwaManageGrid.JiwaCurrencyCellType();
         currcell.CurrencySymbol = "$";
         currcell.DecimalPlaces = 2;
         currcell.DecimalSeparator = ".";
         currcell.FixedPoint = true;
         currcell.LeadingZero = FarPoint.Win.Spread.CellType.LeadingZero.Yes;
         //currcell.MaximumValue = 999999999999;
         //currcell.MinimumValue = 1;
         currcell.NegativeFormat = FarPoint.Win.Spread.CellType.CurrencyNegativeFormat.SignSymbolSpaceBefore;
   
         currcell.NegativeRed = true;
         currcell.PositiveFormat = FarPoint.Win.Spread.CellType.CurrencyPositiveFormat.CurrencySymbolBefore;
         currcell.Separator = ",";
         currcell.ShowCurrencySymbol = true;
         currcell.ShowSeparator = true;
         
         this.JiwaGrid1.ActiveSheet.Columns["NetLinetotal"].CellType = currcell;
         //this.JiwaGrid1.ActiveSheet.Columns["NetLinetotal"].ForeColor = Color.Red;

      }