Inventory browser... bug?

troubleshooting
1

Hello there

We are stuck on an issue that we have not yet managed to resolve.
It is about the existence of products in order to Inventory Browser
Our actual existence is visualized in multiples of 8, and we cannot understand this logic.

1
11276×579 181 KB

2
21278×313 77.1 KB

3
31284×601 163 KB

4
41280×313 74.2 KB

I checked the answer you gave almost a year ago, (Basic workflow - medical testing facility - #7 by t99 ) however it does not seem to apply in our case, since we have verified and (unless you indicate otherwise) yes, we have updated the version with which we are working.
MicrosoftTeams-image (1)
MicrosoftTeams-image (1)776×315 27.1 KB

Is there something wrong with our logic in the interpretation or maybe it is a bug?

2

Yes, that’s certainly a bug. I have a hunch why that might be happening. Can you show me the En Existencias tab for product 7890?

3

I’m so sorry for the delay Justin.
this is the required tab.

5
51015×542 103 KB

Thanks in advance

4

Unfortunately, that was not what I expected. My hunch is that there’s a JOIN happening between product availability and some other table that is causing the multiply x 8 effect. My next thought is that it’s the Product Supplier or Product Catalog tables. Or even more odd, it could be that t he product availability table itself has duplicate rows. Are you running OpenBoxes on multiple servers all pointing to the same database?

Anyway, it’s not as simple as looking at the SQL query behind this page, because the SQL in this case is being generated by the ORM tool (GORM/Hibernate).

In any case, it’s definitely a bug. I’ll investigate as soon as I can. It might be helpful to do a screenshare at some point if I don’t make any progress.

Justin

5

I trust those hunches to solve this calculation.
about your question, our installation runs on a single instance of the same server.
And of course we are at your service, if you consider it necessary, tell us the time and the means to share the screen of our installation.
Thank you very much Justin

6

Apologies for the long delay in getting back to you. So here’s a sample SQL query that is used to pull data for the inventory browser page. This won’t work on your server because the IDs are specific to my local database.

SELECT productava0_.product_id            AS col_0_0_,
       Sum(productava0_.quantity_on_hand) AS col_1_0_,
       product1_.id                       AS id44_,
       product1_.version                  AS version44_,
       product1_.abc_class                AS abc3_44_,
       product1_.active                   AS active44_,
       product1_.brand_name               AS brand5_44_,
       product1_.category_id              AS category6_44_,
       product1_.cold_chain               AS cold7_44_,
       product1_.controlled_substance     AS controlled8_44_,
       product1_.cost_per_unit            AS cost9_44_,
       product1_.created_by_id            AS created10_44_,
       product1_.date_created             AS date11_44_,
       product1_.default_uom_id           AS default12_44_,
       product1_.description              AS descrip13_44_,
       product1_.essential                AS essential44_,
       product1_.gl_account_id            AS gl15_44_,
       product1_.hazardous_material       AS hazardous16_44_,
       product1_.last_updated             AS last17_44_,
       product1_.lot_and_expiry_control   AS lot18_44_,
       product1_.lot_control              AS lot19_44_,
       product1_.manufacturer             AS manufac20_44_,
       product1_.manufacturer_code        AS manufac21_44_,
       product1_.manufacturer_name        AS manufac22_44_,
       product1_.model_number             AS model23_44_,
       product1_.NAME                     AS name44_,
       product1_.ndc                      AS ndc44_,
       product1_.package_size             AS package26_44_,
       product1_.price_per_unit           AS price27_44_,
       product1_.product_code             AS product28_44_,
       product1_.product_type_id          AS product29_44_,
       product1_.reconditioned            AS recondi30_44_,
       product1_.serialized               AS serialized44_,
       product1_.unit_of_measure          AS unit32_44_,
       product1_.upc                      AS upc44_,
       product1_.updated_by_id            AS updated34_44_,
       product1_.vendor                   AS vendor44_,
       product1_.vendor_code              AS vendor36_44_,
       product1_.vendor_name              AS vendor37_44_,
       (SELECT Max(pc.color)
        FROM   product_catalog_item pci
               LEFT OUTER JOIN product_catalog pc
                            ON pci.product_catalog_id = pc.id
        WHERE  pci.product_id = product1_.id
        GROUP  BY pci.product_id)         AS formula1_
FROM   product_availability productava0_
       INNER JOIN product product1_ ON productava0_.product_id = product1_.id
WHERE  productava0_.location_id = '1'
       AND ( productava0_.product_id IN (
                   'ff80818179f420480179f42ccb010006',
                   'ff80818179f420480179f42ccb550007',
                   'ff80818179f420480179f42ccb820008',
                   'ff80818179f420480179f42ccbad0009',
                   'ff80818179f420480179f42ccbce000a',
                   'ff80818179f420480179f42ccbf3000b',
                   'ff80818179f420480179f42ccc17000c',
                   'ff80818179f420480179f42ccc37000d',
                   'ff80818179f420480179f42ccc66000e',
                   'ff80818179f420480179f42ccc82000f' ) )
GROUP  BY productava0_.product_id

But looking at the SQL, the only way I can see a duplicate row being returned is if the product_availability table had duplicate rows.

Therefore, could you check the product availability table to see if you have any duplicates?