Minimum STOCK Report V2.00

Yes, I have a number in that column that is 13882.50 so it failed for me. I suggest raising it higher as @emre mentioned to 10,3 or just 10,2 … it will future-proof it.

So is there any way to solve the first problem with the code from first post?
@emre
The Report with the code from first post the Physical Stock is always 8 Items higher than the real Stock.

becuase I dont care how money something costs, if I liked I buy it… LOL…

seriousley, becasue i am not that expert with SQL… I am just an amateur…

Thanks!!!

G.

Sorry guys… as we here in brasil rearely pay something as much as 9999, sepcially not for food, so that is why I kept my numbers inside 6,2…

G.

Dear Keven, I cannot imagine why is that happening to you, my stock is fine… and its the same code I pasted here…

can you send us some screen ofyour inventory ites configuratin? do you have some multipliers in there?

Thanks!!!

G.

its showing correctly for me too. My stock was 18 it showed 18

@QMcKay I’ve recommended 3 decimals as these fields supports 3 decimal places.

@Kevin … additionally be sure you’re looking at that report after ending work period.

2 Likes

I will post the screen when I am back in my bar. Thank you all in advance.

OK… I have bed oing some test and everything seems to work fine…

FINALLY, I hope (LOL) I have my stock working, with both units in some cases and one unit and an extra unit in others. I call extra units the new set of infinite units added in v4.1.74.

here a sample:

I did a mix between @Jesse´s long_why_should_I_change_my_mind posts and my old version of managing stock, and arrived to a nice way… I some cases like beer I have only one unit (base) and I have an extra unit (usually the unit in wich I buy). In some other cases I have a base unit in wich I sell and have my recepies, trans unit (unit in wich I see my stock like cheese). And third case I have some interesting stock items that hae both base and trans unit and an extra unit… NOW I AM HAPPY…

Thanks to all that helped me thru this hard time…

G.

2 Likes

Here are some pictures


Product Settings

Number in Inventory Report (real amount)

Amount showed in Minimo Report

your are showing a product screen, could you please show an inventory screen for KANDI MALZ?

Thanks!!!

G.

PS between those two reports I see a lot of tems that should match, but the number are not even close
Bitburger, Fransziskaner… all completely wrong…

G

did u mean this screen?

Yepp a lot of other numbers are wrong because i didn´t set Minimo and Ideal in custom Tags I think.

that has absolutely nothing to do with your numbers. Those tags only are used to compare actual stock to these tags and if your stock is less it will appear on report.

Inventory items definitio screen is what I would like to see… to check your multipliers…

g.

I didn´t know which screen you mean can you send send me a sample?
In Samba POS or in SQL Server?

the screen where you define your inventory items.

G.

This one

wait a minute…

you did some changes in that report…

so… I cant tell there what is wrong… in mine (copy paste code) all works… If you copied, pasted and then MADE changes… unles you copy paste it here to see changes we will not be able to guess…

There are 4 columns… NAME, Minimun stock, Ideal Stock and REAL STOCK. there is no DIFERENZ…

THIS IS THE UNTOUCHED CODE:

SELECT
  [InventoryItems].[Name],
    CONVERT(Int,
	        CASE WHEN CharIndex('{"TN":"StockMinimo","TV":"',cast([CustomTags] as nvarchar(4000))) <> 0
	        THEN
	             CONVERT(Integer,substring(cast([CustomTags] as nvarchar(4000)),CharIndex('{"TN":"StockMinimo","TV":"',
				 cast([CustomTags] as nvarchar(4000)))+26,CharIndex('"',cast([CustomTags] as nvarchar(4000)),
                 CharIndex('{"TN":"StockMinimo","TV":"',cast([CustomTags] as nvarchar(4000)))+26)-
				 CharIndex('{"TN":"StockMinimo","TV":"',cast([CustomTags] as nvarchar(4000)))-26))
			ELSE
			     '0'
		    END) as [StockMinimo],
   CONVERT(Int,
	        CASE WHEN CharIndex('{"TN":"StockIdeal","TV":"',cast([CustomTags] as nvarchar(4000))) <> 0
	        THEN
	             CONVERT(Integer,substring(cast([CustomTags] as nvarchar(4000)),CharIndex('{"TN":"StockIdeal","TV":"',
				 cast([CustomTags] as nvarchar(4000)))+25,CharIndex('"',cast([CustomTags] as nvarchar(4000)),
                 CharIndex('{"TN":"StockIdeal","TV":"',cast([CustomTags] as nvarchar(4000)))+25)-
				 CharIndex('{"TN":"StockIdeal","TV":"',cast([CustomTags] as nvarchar(4000)))-25))
			ELSE
			     '0'
		    END) as [StockIdeal],

  CONVERT(Int,CASE WHEN [BaseUnit] = [TransactionUnit] THEN
        (ISNULL([PhysicalInventory],([InStock]+[Added]-[Removed]-[Consumption]))+
		ISNULL((SELECT TOP 1 [Quantity] FROM [InventoryTransactions]
	     WHERE [InventoryItem_Id] = [PeriodicConsumptionItems].InventoryItemId AND [Date] >= CONVERT(varchar(10),GETDATE(),121)
	     ORDER BY [Id] DESC),0))
      ELSE
        ISNULL([PhysicalInventory],ISNULL(([InStock]+[Added]-[Removed]-[Consumption]),0))*ISNULL([UnitMultiplier],0)
	  END) as Stockfisico

FROM [InventoryItems]

LEFT OUTER JOIN [MenuItems] on [MenuItems].[Name] = [InventoryItems].[Name]
LEFT OUTER JOIN [PeriodicConsumptionItems] on ([PeriodicConsumptionItems].[InventoryItemName]=[InventoryItems].[Name] AND [PeriodicConsumptionId] = (SELECT TOP 1 [Id] FROM [PeriodicConsumptions] ORDER BY [Id] DESC))

WHERE CONVERT(Int,
	        CASE WHEN CharIndex('{"TN":"StockMinimo","TV":"',cast([CustomTags] as nvarchar(4000))) <> 0
	        THEN
	             CONVERT(Integer,substring(cast([CustomTags] as nvarchar(4000)),CharIndex('{"TN":"StockMinimo","TV":"',
				 cast([CustomTags] as nvarchar(4000)))+26,CharIndex('"',cast([CustomTags] as nvarchar(4000)),
                 CharIndex('{"TN":"StockMinimo","TV":"',cast([CustomTags] as nvarchar(4000)))+26)-
				 CharIndex('{"TN":"StockMinimo","TV":"',cast([CustomTags] as nvarchar(4000)))-26))
			ELSE
			     '0'
		    END) <> 0
      AND
      ISNULL([PhysicalInventory],ISNULL([InStock]+[Added]-[Removed]-[Consumption],0))*[TransactionUnitMultiplier] <= 
      CONVERT(Integer,
	        CASE WHEN CharIndex('{"TN":"StockMinimo","TV":"',cast([CustomTags] as nvarchar(4000))) <> 0
	        THEN
	             CONVERT(Integer,substring(cast([CustomTags] as nvarchar(4000)),CharIndex('{"TN":"StockMinimo","TV":"',
				 cast([CustomTags] as nvarchar(4000)))+26,CharIndex('"',cast([CustomTags] as nvarchar(4000)),
                 CharIndex('{"TN":"StockMinimo","TV":"',cast([CustomTags] as nvarchar(4000)))+26)-
				 CharIndex('{"TN":"StockMinimo","TV":"',cast([CustomTags] as nvarchar(4000)))-26))
			ELSE
			     '0'
		    END)

ORDER BY [InventoryItems].[Name]
1 Like

I didn´t change your code.

I only change this:

[Stock a Reponer:2, 1, 1, 1, 1 ]
Item|auf Lager|Minimal|Ideal|Differenz
@@StockMinimo

just translation into German in Edit Report under Template

Ok, DIFERENZ means difference, I do speak a little german, and in my report there is NO difference…
just REAL stock, the last column is ment to be REAL stock… if you copied and pasted then the last column should show you exactly the same value as the INVENTORY REPORT that came with SAMBA, specially that you have no multiplier there…

G.

PD looking closer to your report, you did not just copy paste, since you have 4 columns with numbers and in my report there are only 3… something must had happend there…

G.

This is what I get in SQL Query
Same Problem

Total Strange … I also didn´t find the error