Howdy, Stranger!
Categories
- All Categories
- 73 General
- 73 Announcements
- 66.6K Microsoft Dynamics NAV
- 18.7K NAV Three Tier
- 38.4K NAV/Navision Classic Client
- 3.6K Navision Attain
- 2.4K Navision Financials
- 116 Navision DOS
- 851 Navision e-Commerce
- 1K NAV Tips & Tricks
- 772 NAV Dutch speaking only
- 617 NAV Courses, Exams & Certification
- 2K Microsoft Dynamics-Other
- 1.5K Dynamics AX
- 320 Dynamics CRM
- 111 Dynamics GP
- 10 Dynamics SL
- 1.5K Other
- 990 SQL General
- 383 SQL Performance
- 34 SQL Tips & Tricks
- 35 Design Patterns (General & Best Practices)
- 1 Architectural Patterns
- 10 Design Patterns
- 5 Implementation Patterns
- 53 3rd Party Products, Services & Events
- 1.6K General
- 1K General Chat
- 1.6K Website
- 83 Testing
- 1.2K Download section
- 23 How Tos section
- 252 Feedback
- 12 NAV TechDays 2013 Sessions
- 13 NAV TechDays 2012 Sessions
Options
Update Posted Sales Transaction Fact Table
jasonkwp
Member Posts: 12
in SQL General
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
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
0
Best Answers
-
Wisa123
Member Posts: 308
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.Austrian NAV/BC Dev5 -
kriki
Member, Moderator Posts: 9,121
[Topic moved from 'Navision Financials' forum to 'SQL General' forum]
Regards,Alain Krikilion
No PM,please use the forum. || May the <SOLVED>-attribute be in your title!5
Answers
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
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.
No PM,please use the forum. || May the <SOLVED>-attribute be in your title!