Update Posted Sales Transaction Fact Table

jasonkwp

I cannot use ISNULL , If you look at my screenshot I am trying to update the actual sales cost , sales support and sales cost , all these dimensions related to the sales amount(actual) , cost amount (actual) in the Value entry table

my script to update the fields marked in orange on my screenshot is

UPDATE [dbo].[Posted Sales Transactions_R]
SET [Cost Amount] = ([Cost Amount] + [Sales Costs (Actual)])
WHERE [Item Charge No.] = 'FOBB'

UPDATE [dbo].[Posted Sales Transactions_R]
SET [Sales Costs (Actual)] = ''
WHERE [Item Charge No.] = 'FOBB'

UPDATE [dbo].[Posted Sales Transactions_R]
SET [Sales Costs FCY] = 0
WHERE [Item Charge No.] = 'FOBB'

But the script is still not adding the sales cost amount into that fields. the sales cost amount is -198,132.61 , this amount should be in the Sales Cost , Sales Support and Actual Sales Cost fields. in short , FOBB is calculated as our Freight Charges

My posted sales transactions table is a fact table within my cube

Wisa123

1.) Why not change these Values in C/AL ? Direct SQL Updates are kinda dangerous in terms of data validation.

2.) There are no FlowFields in the Value Entry Table, so I don't see any of your changes to [Posted Sales Transactions_R] to affect the Value Entries.

kriki

[Topic moved from 'Navision Financials' forum to 'SQL General' forum]

jasonkwp

my screenshot Indicates that i am trying to update the actual sales cost , sales support and sales cost , all these dimensions related to the sales amount(actual) , cost amount (actual) in the Value entry table

my script to update the fields marked in orange on my screenshot is

UPDATE [dbo].[Posted Sales Transactions_R]
SET [Cost Amount] = ([Cost Amount] + [Sales Costs (Actual)])
WHERE [Item Charge No.] = 'FOBB'

UPDATE [dbo].[Posted Sales Transactions_R]
SET [Sales Costs (Actual)] = ''
WHERE [Item Charge No.] = 'FOBB'

UPDATE [dbo].[Posted Sales Transactions_R]
SET [Sales Costs FCY] = 0
WHERE [Item Charge No.] = 'FOBB'

But the script is still not adding the sales cost amount into that fields. the sales cost amount is -198,132.61 , this amount should be in the Sales Cost , Sales Support and Actual Sales Cost fields. in short , FOBB is calculated as our Freight Charges

My posted sales transactions table is a fact table within my cube

Wisa123

1.) Why not change these Values in C/AL ? Direct SQL Updates are kinda dangerous in terms of data validation.

2.) There are no FlowFields in the Value Entry Table, so I don't see any of your changes to [Posted Sales Transactions_R] to affect the Value Entries.

kriki

[Topic moved from 'Navision Financials' forum to 'SQL General' forum]