Inventory Purchase Improvements

QMcKay · September 9, 2014, 8:26pm

Thanks @emre, that helps!

Nearly there…

QMcKay · September 10, 2014, 3:23am

@emre, I’m very close, but I’ve run into something I can’t figure out.

EDIT: I figured it out, so you can disregard this post.

I am able to Create the following, with the correct values for all fields:
Account Transaction Document
Account Transaction

But I’m having trouble with:
Account Transaction Values

Specifically, the Target Account ID (or the Source Account ID, depending on how you look at it). I can’t figure out how to retrieve it.

To be clear, I can get the Supplier Account ID (which I call the Source, since the Document defines it that way), but I don’t know how to get the Payment Account ID (i.e. Cash/Drawer, Wallet, Bank, etc.) This is because the the Default Target Account ID in the Transaction Type definition is ZERO… as it should be, since I don’t know which Account I will pay from.

I know this works properly within SambaPOS, because I have buttons for the different Payment types (shown previously), but I can’t figure out how you manage to derive it.

Can you assist?

Jesse · September 10, 2014, 12:25pm

VERY NICE @QMcKay love the design flow you made. You have inspired me a ton with my own solution. I just wish I was as proficient and fast as you with it haha.

QMcKay · September 10, 2014, 5:07pm

Done! Less than a week later, and my brain hurts a little bit, but I’m satisfied with the result, so I think it’s time to celebrate! Then…

Some more bug testing and cleanup (2-3 days) and I’ll release this if anyone wants it.

This was all done using what is available in the SambaPOS Database, with no alterations or additions to tables, etc.

Inventory Documents

Some are fully paid so they show no Payout controls, while others have amounts remaining to be paid. By the way, those Payout buttons and their colors come from SambaPOS as defined in the Document Types section of Accounts… looks like I may need to change the colors

Inventory Items

Filtered by Product Type (GroupCode) and Supplier (Warehouse) - also notice the Previous TX Documents section is hidden (collapsed).

QMcKay · September 10, 2014, 5:33pm

@emre, the Z-index property doesn’t appear to work… the < CLOSE > button has a value of 500, while the HTML Widget has a value of 1, but the button is still hidden…

Jesse · September 10, 2014, 5:35pm

I noticed this behavior too. Its unique to HTML WIDGET. I am betting its how it draws the webpage. Technically it looks like it is in front of the widget its just not showing it in front of the imbedded browser.

emre · September 10, 2014, 11:30pm

Yes @Jesse is right. I don’t have too much control on how Internet explorer draws itself.

QMcKay · September 11, 2014, 6:33pm

Ok, thanks @emre… no worries; not a big deal.

I have a request… Can we get the following for Inventory Items:

Tax Template mappings
Default Price/Cost for Transaction Unit and possibly Base Unit

Thanks,
Q

gerlandog · September 11, 2014, 8:15pm

Here you go!!!

SELECT [InventoryItemName],
      CASE WHEN [BaseUnit] = [TransactionUnit] THEN
        CONVERT(INT,ISNULL([PhysicalInventory],([InStock]+[Added]-[Removed]-[Consumption])))
      ELSE
        CONVERT(INT,(CONVERT(numeric(6,2),ISNULL([PhysicalInventory],([InStock]+[Added]-[Removed]-[Consumption])))*[UnitMultiplier]))
      END as Stockfisico,
	  [BaseUnit] as Unidad,
	  FORMAT(CONVERT(numeric(6,2),[Cost]),'C') as CostoUnit,
	  FORMAT(CONVERT(numeric(6,2),([Cost]*ISNULL([PhysicalInventory],([InStock]+[Added]-[Removed]-[Consumption])))),'C') as CostoTOT
      
FROM [PeriodicConsumptionItems]

LEFT OUTER JOIN [InventoryItems] ON [InventoryItemId] = [InventoryItems].[Id]

WHERE [WarehouseConsumptionId] = (SELECT TOP 1 [Id] FROM [PeriodicConsumptions] ORDER BY [Id] DESC)
      AND
	  CONVERT(numeric(6,2),ISNULL([PhysicalInventory],([InStock]+[Added]-[Removed]-[Consumption]))) >= 0.05

ORDER BY [InventoryItemName]

QMcKay · September 11, 2014, 8:39pm

Thanks @gerlandog, I’ve seen that report of yours, very nice. But I’m asking @emre for these additions to the Inventory section:

I want the above additions so that I can pre-populate the following fields when purchasing product from a supplier:

gerlandog · September 11, 2014, 8:42pm

Sorry, I uderstood that you wanted to obrain that info…

My bad…

G.

QMcKay · September 11, 2014, 10:50pm

@emre, in addition, can you please make the End of Day Records and the Warehouses screen obey the Warehouse [SortOrder], so that my top Warehouse is selected and displayed first. Currently, the displays are ignoring the [SortOrder], and are being displayed according to their order of creation which is [Id].

emre · September 12, 2014, 3:47pm

@QMcKay

Warehouse sort order fixed.
For end of day records you can click Change Sort order to see latest one on top.
I’ll skip Tax Template request since we won’t handle that with templates. It is not our final decision but we’ll probably implement it by amount entry instead of configuring rates. As we need to stick to supplier’s tax calculation method to find the exact tax amount I think entering tax amounts (and other calculations) by hand is the most convenient solution.
Default prices done.

Jesse · September 12, 2014, 3:50pm

Really like the default cost option!

QMcKay · September 12, 2014, 6:17pm

Thanks much @emre for the fixes and additions! The Tax Template thing isn’t a big deal, it would just help with auto-populate of the Tax calculation for defaults, just like the Price.

I had posted another request in another thread that got off topic, so I’ll move it here. It has to do with Units, and I have a request to add another Unit-Type…

QMcKay · September 12, 2014, 7:09pm

So this brings up something else that I want to do, and now I realize it won’t be difficult, since I already have Inventory purchases working in PHP. And the controls are already in place to do this:

I have items that I purchase, and by default, they land at SHOP Warehouse (from whichever Supplier Warehouse). So far so good. I already have all of this built.

Frequently, I can’t hold all the Inventory at my Shop, so I move a good deal of it to my house storage, which I call BODEGA. So now I just need to create Inventory Documents and Transactions to transfer from SHOP to BODEGA. This should be simple enough to do - it’s just a matter of changing Source and Target and executing the transaction!

Here is the request:

@emre, I don’t suppose we could get a third unit type (and associated Multiplier) for Inventory Items? Perhaps Small Unit (piece), Medium Unit (pack), and Large Unit (case).

The issue is that I don’t transfer a Case or a Piece of Inventory from one place to another, instead I use a unit that is in-between: a pack or can or tray, etc.

In my implementation using mySQL, I have 3 unit types which I call Packages, whereas in SambaPOS there are only 2. But I want to completely abandon mySQL and use only the data in SambaPOS, so that’s why I’m requesting another Unit-Type.

This actually applies to many products, like:

Ham: Case, Package (6), Slice (30)
Sauce: Case, Can (6) , mL (2000)
Croissant: Case, Flat/Tray (6), Piece (8)
Drink Lids: Case, Sleeve (20), Lid/Piece (100)

Examples…
For Croissant:
There are 8 pieces per Tray, and 6 Trays per Case; in total 48 pieces.
For Lids:
There are 100 lids per Sleeve, and 20 sleeves per Case; in total 2000 lids.

emre · September 13, 2014, 12:37am

I can’t decide now if I should hardcode additional units or let people to configure units like we configure portions. I should think about the future design of that. I can think some cases where we need 4 units.

QMcKay · September 13, 2014, 3:35pm

Are you thinking of Unit control on a per-item basis (like Portions are now), or simply allow for Unit control over a global configuration? I think Global control for all Items would be good enough.

Allow for users to create as many Units as they want (globally), and they must use them from the base-upwards. If the 3rd or 4th or 5th Units are unused for the Item, then they are ignored.

Jesse · September 13, 2014, 3:39pm

I think the global method would be the best approach as well. Less chance of someone messing it up. And like @qmckay said just make it so unused ones are ignored.

The only hurdle I see is how it would affect the purchase orders and how that would affect cost. I don’t know if cost calculations require specific fields or not for units.

It seems to me like it shouldn’t matter really unless someone just configures their units wrong which could be an increased situation with adding more optional units. I can see where someone would not configure it correctly or get confused on how it should be configured. Of course this can happen now as well so really that would not be any different.

gerlandog · September 13, 2014, 3:49pm

I think UNITS is still a little green…
At first (and I had a hard time now to change that) I configured this:

BEER, BOX (main unit), UNIT (Second Unit), 24 (Unit Multiplier)...

So purchases where ok, I allways purchase at least 1 BOX of beer… 24 units…

But all reports showed me BOXes… and Stock would show me 0,71 of BOX, so that converted into UNIT was 17,036. (how could I have 0,036 of a bottle?

Maybe if I would have started using it reversed… like

BEER, UNIT (main unit), BOX (Second Unit), 0,041666 (Unit Multiplier)...

Maybe I got this part all wrong…

G.