Hmm I forgot to mention Calculation.Amount can be a rate depending on selected calculation method.
Another snippet to update Currency and Accounts:
--------------------------
-- Update Foreign Currency Template and Accounts
--------------------------
declare @currencyNameOld varchar(10) = 'USD'
declare @currencyName varchar(10) = 'HNL'
declare @currencyFormat varchar(10) = 'L {0:N}'
declare @currencyXR decimal (15,6) = 23.000000
declare @currencyInverted int = 1
declare @currencyID int = (SELECT [Id] FROM [ForeignCurrencies] WHERE [Name] = @currencyNameOld)
-- change Foreign Currency
UPDATE [ForeignCurrencies] SET
[Name] = @currencyName
,[CurrencySymbol] = @currencyFormat
,[ExchangeRate] = @currencyXR
,[InverseExchangeRate] = @currencyInverted
WHERE [Id] = @currencyID
-- swap Currency for all Accounts
UPDATE [Accounts] SET
[ForeignCurrencyId] = 99999
WHERE [ForeignCurrencyId] = @currencyID
UPDATE [Accounts] SET
[ForeignCurrencyId] = @currencyID
WHERE [ForeignCurrencyId] = 0
UPDATE [Accounts] SET
[ForeignCurrencyId] = 0
WHERE [ForeignCurrencyId] = 99999
I ran the scripts and it does a pretty good job overall for modifying the bulk of Accounts which would otherwise be a very tedious job.
In my case, I still need to think carefully about Inventory, because most of my Suppliers charge for their products in HNL while only a few charge in USD.
I left out the following conversions because of this:
,('InventoryItems','DefaultBaseUnitCost',0)
,('InventoryItems','DefaultTransactionUnitCost',0)
,('InventoryItemUnits','DefaultCost',0)
I think there still might be some other manual work involved here in regard to Inventory Purchases (the Inventory Docs and associated Account Transaction Types). Going forward, it will probably also adversely affect the Cost Calculations.
I think I need to create some new Account Transaction Types (using HNL) for use in Inventory Docs and see how Purchases and Costs are affected going forward. The Inventory Cost/Profit/Loss is a very important part of my system.
1 Like
I think the default Warehouses Screen should probably work just fine for handling Payments in different Currencies and Exchange Rates. However, as I’ve expressed in the past, I don’t like the way it works so I continue to use my PHP Inventory System.
The big difference between the 2 methods is that with Warehouses, we are forced to choose the Doc (Payment Method), then add Inventory Items. This assumes the entire Purchase Transaction will be paid from a single Account, which in most cases is true. But sometimes (actually often) I need to do partial Payments for an Invoice from more than 1 Account, and sometimes those Accounts also use different Currencies.
With my PHP system, the operation is “backwards” in that I choose Inventory Items first while ensuring the Supplier Entity is correctly assigned, so all Items go into a single Inventory Doc. Then I can make (partial) Payments to that Document by multiple Methods …
Unfortunately, the PHP system was built at a time when Inventory Document Types were not functional (v4?), so I instead used Account Document Types and I fashioned the SQL to insert transactions using those Account Documents.
Now I think I need to switch the system to use Inventory Docs which have linked Account Transaction Types and change all the SQL to accomodate this. The problem is that it has been so long since I looked at the code and it is scary to say the least, being that the Commenting Documentation is poor and there is a mess of things going on that I am having trouble deciphering LOL 
I am confident that I will be able to work it out eventually, but until I do, I can’t really switch the system currencies.
Maybe what I am asking for in a roundabout way is whether or not there are any near-future plans to change how Warehouses Screen works so that maybe it mirrors my PHP system instead.
OR perhaps if we had some support in GraphQL for creating Inventory Documents which automatically fire the associated Account Transactions. Does that sound plausible @emre?
I worked it out and now my Inventory Purchase Screen is working better than ever! Yay!
Anyway, I was doing some more testing having to do with Account Statements and the Account Balances.
Some of the Balances looked a little funky after conversion, so I pulled up some Tickets like this one:
The issue is rather obvious… the Discount applied via Order Tag is far too high, which makes the Ticket Total NEGATIVE. A converted value might be 0.50 USD for 10 HNL.
So… now I need to find a way to update those Tag Values, which are in JSON. Thankfully, I am using SQL 2016 which has some support for JSON fields so I hope it won’t be too much of a problem.
2 Likes
Crude update I am testing using a #TEMP table, showing some JSON functions (it works):
DROP TABLE IF EXISTS #TAGS
CREATE TABLE #TAGS
(
[Id] int
,[Tags] nvarchar(max)
)
DELETE FROM #TAGS
INSERT INTO #TAGS
SELECT
[Id]
,[OrderTags]
FROM [Orders]
WHERE ([OrderTags] like '%VIP Discount%' OR [OrderTags] like '%Happy Hour Discount%')
--SELECT * FROM #TAGS
SELECT
[Id]
--,[Tags]
,TagName
,TagPrice
FROM #TAGS
cross apply
openjson(#TAGS.[Tags], '$')
with (
TagName varchar(50) '$.TN'
, TagPrice varchar (50) '$.PR'
) as Tags
WHERE 1=1
AND TagPrice is not null
AND TagPrice='-10'
--ORDER BY TagName
UPDATE T
SET Tags = JSON_MODIFY(T.Tags, '$[0].PR', -1.00)
FROM #TAGS T
cross apply
openjson(T.[Tags], '$')
with (
TagName varchar(50) '$.TN'
, TagPrice varchar (50) '$.PR'
) as Tags
WHERE TagPrice=-10--JSON_VALUE(Tags,'$[0].PR')=-10
GO
SELECT * FROM #TAGS
1 Like
The reason I said it was crude was because it would only handle Array Index 0.
The JSON data is stored as an Array, and there is no guarantee that the Tag Price we want to update is always at Index 0. So we must iterate all Indexes to make sure we got them all. Here is an example where the “Addons” Tag and it’s Price are in position 5 (Index 4) …
[{"OI":4,"OK":"040030","Q":1,"TF":true,"TN":"Proteins","TV":"Pork ","UI":2},{"OI":1,"OK":"070060","Q":1,"TF":true,"TN":"Veggies","TV":"Pepper Green","UI":2},{"OI":1,"OK":"070030","Q":1,"TF":true,"TN":"Veggies","TV":"Onion Red","UI":2},{"OI":3,"OK":"060020","Q":1,"TF":true,"TN":"Cheese","TV":"Swiss","UI":2},{"OI":5,"OK":"050010","PR":"1.50","Q":1,"TF":true,"TN":"Addons","TV":"Bacon","UI":2},{"OI":1,"OK":"070020","Q":1,"TF":true,"TN":"Veggies","TV":"Tomato","UI":2},{"OI":1,"OK":"070010","Q":1,"TF":true,"TN":"Veggies","TV":"Lettuce","UI":2},{"OI":10,"OK":"090060","Q":1,"TF":true,"TN":"Sauces","TV":"Whiskey Sauce","UI":2}]
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
I have 19 Order Tag Groups, so it means we need to run from Index 0 through 18. To do this programatically we need to build dynamic SQL Statements and execute them. This is because the JSON PATH (ie. '$[0]' ) needs to be a string literal (it cannot be a variable).
declare @XR decimal(5,2) = 20.00 -- conversion rate
declare @t varchar(10) = '0' -- iterator for Order Tag Group
declare @tmax int = 20 -- count of Order Tag Groups
declare @SQL nvarchar(max) = '' -- SQL Statement
DROP TABLE IF EXISTS #ORDERTAGS
CREATE TABLE #ORDERTAGS
(
[Id] int
,[Tags] nvarchar(max)
,[TagName] nvarchar(max)
,[TagPrice] nvarchar(max)
,[TagPriceConv] nvarchar(max)
)
DELETE FROM #ORDERTAGS
print 'INSERTING ...'
INSERT INTO #ORDERTAGS
SELECT
[Id]
,[OrderTags]
,''
,0
,0
FROM [Orders]
WHERE 1=1
AND [OrderTags] like '%Discount%' OR [OrderTags] like '%Addon%'
/*
print 'UPDATE 0 ...'
UPDATE T
SET Tags = JSON_MODIFY(T.Tags, '$[0].PR', FORMAT(Tags.[TagPrice]/@XR,'0.00') )
, TagName = Tags.TagName , TagPrice=Tags.TagPrice , TagPriceConv=FORMAT(Tags.TagPrice/@XR,'0.00')
FROM #ORDERTAGS T
cross apply
openjson(T.[Tags], '$[0]')
with (
TagName varchar(50) '$.TN'
, TagPrice varchar (50) '$.PR'
) as Tags
WHERE Tags.TagPrice is not null
*/
WHILE @t <= @tmax
BEGIN
SET @SQL = 'UPDATE T'
SET @SQL = @SQL + ' SET Tags = JSON_MODIFY(T.Tags, ''$['+@t+'].PR'', FORMAT(Tags.[TagPrice]/'+convert(varchar(20),@XR)+',''0.00'') )'
SET @SQL = @SQL + ' , TagName = Tags.TagName , TagPrice=Tags.TagPrice , TagPriceConv=FORMAT(Tags.TagPrice/'+convert(varchar(20),@XR)+',''0.00'')'
SET @SQL = @SQL + ' FROM #ORDERTAGS T'
SET @SQL = @SQL + ' cross apply'
SET @SQL = @SQL + ' openjson(T.[Tags], ''$['+@t+']'')'
SET @SQL = @SQL + ' with ('
SET @SQL = @SQL + ' TagName varchar(50) ''$.TN'''
SET @SQL = @SQL + ' , TagPrice varchar(50) ''$.PR'''
SET @SQL = @SQL + ' ) as Tags'
SET @SQL = @SQL + ' WHERE Tags.TagPrice is not null'
print 'EXEC : '+@SQL
EXECUTE sp_executesql @SQL
SET @t = @t+1
END
print 'RESULTS ...'
SELECT * FROM #ORDERTAGS ORDER BY [TagName]
-----------------------------
-- commit updates to [Orders] table
-----------------------------
/*
print 'Committing changes to [Orders] table ...'
UPDATE o
SET [OrderTags] = m.[Tags]
FROM [Orders] o
JOIN #ORDERTAGS m on m.[Id] = o.[Id]
*/
If we uncomment the last few lines, then the changes will be commited to the [Orders] table. Let’s pull up that same Ticket to see if it worked …
SUCCESS!!! 
This script properly converts values as expected …
---------------------------------
-- Change the Database Decimal Precision in SambaPOS before running this,
-- and ensure SambaPOS is closed. For example, I changed the precision from 2 to 6.
-- The setting is in Settings > Program Settings > General > Currency Decimals > Database Decimals
---------------------------------
-- OLD Foreign Currency Name
declare @currencyNameOld varchar(10) = 'USD'
-- settings for NEW Foreign Currency
declare @currencyName varchar(10) = 'HNL'
declare @currencyFormat varchar(10) = 'L {0:N}'
declare @currencyXR decimal (15,6) = 23.000000
declare @currencyInverted int = 1
-- the Exchange Rate of the OLD Currency
-- used to recalculate Account Transaction Values
declare @XR decimal (15,6) = 20.000000
print 'Conversion Rate / Inverted : ' + convert(varchar(20),@XR) + ' / ' + convert(varchar(20),1/@XR)
declare @tbl_ConversionItems table
(
[ID] INT IDENTITY(1,1) NOT NULL
,[TableName] varchar(255)
,[TableField] varchar(255)
,[ConvertFlag] int default 1
)
print 'Clearing ConversionItems ...'
DELETE FROM @tbl_ConversionItems
print 'Populating ConversionItems ...'
INSERT INTO @tbl_ConversionItems
VALUES
-- Calculations
('CalculationTypes','Amount',1)
,('CalculationTypes','MaxAmount',1)
,('CalculationTypes','Rounding',1)
,('Calculations','Amount',1)
,('Calculations','Rounding',1)
,('Calculations','CalculationAmount',1)
,('Calculations','Rate',0)
-- Payments
,('Payments','Amount',1)
,('Payments','TenderedAmount',1)
,('ChangePayments','Amount',1)
-- Products
,('MenuItemPrices','Price',1)
,('OrderTags','Price',1)
-- Inventory
,('Recipes','FixedCost',1)
,('CostItems','CostPrediction',1)
,('CostItems','Cost',1)
,('CostItems','AvgPrice',1)
,('PeriodicConsumptionItems','Cost',1)
,('InventoryItems','DefaultBaseUnitCost',0)
,('InventoryItems','DefaultTransactionUnitCost',0)
,('InventoryItemUnits','DefaultCost',0)
,('InventoryTransactions','TotalPrice',1)
-- Tickets/Orders
,('Orders','Price',1)
,('Tickets','RemainingAmount',1)
,('Tickets','TotalAmount',1)
-- Transactions
,('AccountTransactions','Amount',1)
,('AccountTransactionValues','Debit',1)
,('AccountTransactionValues','Credit',1)
,('AccountTransactionValues','Exchange',1)
-- Exchange Rates
,('AccountTransactions','ExchangeRate',1)
,('Tickets','ExchangeRate',1)
,('ChangePayments','ExchangeRate',1)
,('Payments','ExchangeRate',1)
,('Calculations','ExchangeRate',1)
--SELECT * FROM @tbl_ConversionItems
------------------------------------
-- Perform Conversions
------------------------------------
declare @SQL nvarchar(max) = ''
declare @tblName varchar(100) = ''
declare @tblField varchar(100) = ''
declare @pConvertFlag int = 1
declare @i int = 0
declare @maxid int = 0
declare @updateCount int = 0
SET @maxid = (SELECT max([ID]) FROM @tbl_ConversionItems)
SET @updateCount = (SELECT count(*) FROM @tbl_ConversionItems WHERE [ConvertFlag]=1)
print 'ConversionItems Count : ' + convert(varchar(10),@maxid)
print 'Field Update Count : ' + convert(varchar(10),@updateCount)
print 'Performing Field Conversions ...'
WHILE @i <= @maxid
BEGIN
SET @tblName = (SELECT [TableName] FROM @tbl_ConversionItems WHERE [ID] = @i)
SET @tblField = (SELECT [TableField] FROM @tbl_ConversionItems WHERE [ID] = @i)
SET @pConvertFlag = (SELECT isnull([ConvertFlag],0) FROM @tbl_ConversionItems WHERE [ID] = @i)
IF @pConvertFlag = 1
BEGIN
SET @SQL = 'UPDATE ['+@tblName+'] SET ['+@tblField+'] = ['+@tblField+']/'+convert(varchar(50),@XR)
SET @SQL = 'UPDATE ['+@tblName+'] SET ['+@tblField+'] = ['+@tblField+']*'+convert(varchar(50),1/@XR)
print 'EXECUTING '+convert(varchar(10),@i) + ': ' +@SQL
EXECUTE sp_executesql @SQL
END
SET @i = @i + 1
END
print 'Field Conversion Complete!'
/*
--------------------------
-- Update Foreign Currency Template and Accounts
-- this should be done prior to creating any NEW Accounts (ie. Rounding Accounts)
-- You may not want to run this part on all Accounts. You may not want to run it
-- on ANY Accounts. For example, in my case, it only makes sense to run this for
-- Expense and Supplier Accounts.
--------------------------
declare @currencyID int = (SELECT [Id] FROM [ForeignCurrencies] WHERE [Name] = @currencyNameOld)
print 'Updating OLD Currency ('+@currencyNameOld+') to NEW Currency ('+@currencyName+') ...'
-- Update Foreign Currency
UPDATE [ForeignCurrencies] SET
[Name] = @currencyName
,[CurrencySymbol] = @currencyFormat
,[ExchangeRate] = @currencyXR
,[InverseExchangeRate] = @currencyInverted
WHERE [Id] = @currencyID
print 'Swapping Account Currency parameters to use NEW Currency ...'
-- Update Accounts to swap Currency
UPDATE a SET
a.[ForeignCurrencyId] = 99999
FROM [Accounts] a
JOIN [AccountTypes] act on act.[Id]=a.[AccountTypeId]
WHERE [ForeignCurrencyId] = @currencyID
AND act.[Id] IN (SELECT [Id] FROM [AccountTypes] WHERE [Name]='Expense Accounts' OR [Name]='Supplier Accounts')
UPDATE a SET
a.[ForeignCurrencyId] = @currencyID
FROM [Accounts] a
JOIN [AccountTypes] act on act.[Id]=a.[AccountTypeId]
WHERE [ForeignCurrencyId] = 0
AND act.[Id] IN (SELECT [Id] FROM [AccountTypes] WHERE [Name]='Expense Accounts' OR [Name]='Supplier Accounts')
UPDATE a SET
a.[ForeignCurrencyId] = 0
FROM [Accounts] a
JOIN [AccountTypes] act on act.[Id]=a.[AccountTypeId]
WHERE [ForeignCurrencyId] = 99999
AND act.[Id] IN (SELECT [Id] FROM [AccountTypes] WHERE [Name]='Expense Accounts' OR [Name]='Supplier Accounts')
*/
Before and After - General
Conversion Rate used in the Script is 20.000000
I am struggling with what to do with the 2 highlighted Accounts (Cash HNL and Credit Card HNL). Accounts like this were previously configured to use the Default Currency (unassigned [HNL]), but now they are configured to use the NEW Foreign Currency of HNL.
The amounts shown are actually correct for converted values (ie. for Cash HNL 644242.47/20 = 32854.82) however, I would instead want the amounts to NOT be converted, since the proper HNL value should be L 644242.47 for that Account, but it is showing the amount converted to the now Default USD Currency.
@emre, do you have any suggestion for this? It might be a rather moot point when it comes to Payment Accounts as shown in this screen, but for other accounts such as Expense and Supplier Accounts it becomes more important.
Here is a granular view of a Sale and Payment using Cash HNL in the DB, after the conversion is run. I think I might need to constrain the conversion to omit records like this, or do something different with the [Exchange] columns for these cases.
I think what I will try is something like the following, to be run after conversion - what do you think?
UPDATE [AccountTransactions] SET
[ExchangeRate]=20.000000
WHERE [Name]='Payment Transaction [Cash]' OR [Name]='Payment Transaction [Credit Card]' OR [Name]='Change Transaction [Cash]'
UPDATE [AccountTransactionValues] SET
[Exchange]=[Exchange]*20.000000
WHERE [Name]='Payment Transaction [Cash]' OR [Name]='Payment Transaction [Credit Card]' OR [Name]='Change Transaction [Cash]'
Here are the HNL Payment Accounts after conversion, and after the update shown above:
Here is another example, this time with a Supplier Account that previously used the Foreign Currency of USD, but after conversion uses the Default Currency (undefined [USD]). The [AccountTransactions] table shows the correct amount for the Account, while the [AccountTransactionValues] table show an incorrect amount in the [Exchange] column. The Exchange for [Roatan Provision] in [AccountTransactionValues] should be 200 instead of 10.
Other Accounts show incorrect values as well, since they were using Default Currency (undefined [HNL]) and now use Foreign Currency HNL. For example, [gas for geny] and [Sol Gas] show correct USD amounts of $50 each, but the Exchange in [AccountTransactions] is 0.05 when it should be 20, and in [AccountTransactionValues] the Exchange is 50 when it should be 1000.
So this other snippet should handle Accounts which are now configured as Foreign HNL and correctly adjust the 2 “special” accounts which Invoice in USD.
UPDATE [AccountTransactions] SET
[ExchangeRate]=@XR
WHERE 1=1
AND ([Name] like '%Expense%' OR [Name] like '%Supplier%')
AND ([Name] not like '%Accountant%' AND [Name] not like '%Roatan Provision%')
UPDATE [AccountTransactionValues] SET
[Exchange]=[Exchange]*@XR
WHERE 1=1
AND ([Name] like '%Expense%' OR [Name] like '%Supplier%')
Here they are after conversion, and after the update shown above:
GAAAAH! 
I have another problem (and it is a big show-stopper), and I don’t know any way to fix or workaround it.
It has to do with Inventory Purchases and Item Cost. The problem is …
Most of my Inventory Suppliers invoice in HNL, so I have my Inventory Default Costs set up in HNL. But my default currency is now USD. For example, a case of Port Royal Beer (24 bottles) is HNL 452. This makes the Bottle Cost HNL 18.83. I sell it for USD 3.00. So it looks like I am taking a loss …
Any suggestions @emre?
So do you need currency support for inventory transactions?
Yes, please, somehow. Sounds like you might be referring to the Inventory Tx Types and Inventory Doc Types?
I was thinking maybe a currency setting on the Inventory Item itself ([ForeignCurrencyId] column in [InventoryItems]), or maybe in the Warehouse ([Warehouses]) definition?
And [Exchange] and [ExchangeRate] columns maybe in [CostItems] ?
I don’t know what makes the most sense?
You know I use PHP, but it should obviously work in the Warehouses Screen first.
Both won’t work as you sound like purchase anything from any supplier in any currency.
1 Like
Yes, that is possible. I can already Pay an Invoice in multiple Currencies. That isn’t my problem.
My problem is about computing Cost. That was my reasoning for having a Currency parameter in the Inventory Item. It seems to be the most logical choice to me.
The other suggestion of having the parameter in the Warehouse definition seems like the next-most logical choice, since we should be able to assume that the Invoices from a particular Supplier are always in the same Currency.
Anyway, you will probably know what is best. What is your idea on where to put the Currency parameter?
This is what I can do 
and this is how supplier account appears for this transaction.
This is how Bacon is configured. If you want to setup a default cost you can configure it in default currency.
Whatever currency configured for the supplier you can change Currency for a single transaction document so people who purchases an item from multiple suppliers in separate currencies can also benefit from that feature.
1 Like
Yes it is in the latest refresh.
Busy day at the restaurant yesterday so did not have a chance to try this yet.
When I look at it again today, I don’t think this update is going to solve the Cost/Profit issue.
And this part …
No please @emre, that is the issue. I want to be able specify product cost in Foreign HNL Currency. If the Supplier sells products that are priced in HNL, and they invoice in HNL, using a price in default (USD) will not work, because the XR changes daily. For example…
For the Supplier “Cerveceria”, they price and invoice in HNL. The price for a case of Barena (beer) is HNL 452. This rarely changes (maybe 1 or 2 times per year). So if I convert that price today @23 it would be USD 19.65. Next week, the XR changes to 23.5 and now I need to convert HNL 452 again, so it becomes USD 19.23. That is exactly what I want to avoid.
The product price is still HNL 452. I need a way to denote that the pricing for these types of Suppliers/Products (of which there are many) is in HNL Currency, hence why I go back to the idea that there needs to be a Currency Parameter in the Inventory Item configuration.
Busy day at the restaurant yesterday so did not have a chance to try this yet.
When I look at it again today, I don’t think this update is going to solve the Cost/Profit issue.
And this part …
No please @emre, that is the issue. I want to be able specify product cost in Foreign HNL Currency. If the Supplier sells products that are priced in HNL, and they invoice in HNL, using a price in default (USD) will not work, because the XR changes daily. For example…
For the Supplier “Cerveceria”, they price and invoice in HNL. The price for a case of Barena (beer) is HNL 452. This rarely changes (maybe 1 or 2 times per year). So if I convert that price today @23 it would be USD 19.65. Next week, the XR changes to 23.5 and now I need to convert HNL 452 again, so it becomes USD 19.23. That is exactly what I want to avoid.
The product price is still HNL 452. I need a way to denote that the pricing for these types of Suppliers/Products (of which there are many) is in HNL Currency, hence why I go back to the idea that there needs to be a Currency Parameter in the Inventory Item configuration.
Uh OK. I’ll take a look then.