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