Solved

Unable to access all database records through a Generic Inquiry

  • 3 August 2023
  • 9 replies
  • 104 views
MichaelShirk
Rank
Userlevel 2
Badge

I’m attempting to create a Generic Inquiry based on the INLotSerialStatus DAC.
However, it only shows me some of the records that are present in the database.
If I use the DAC schema browser and look at the DAC source data, I see about 2,100 fewer records than what are in the database table. 

I’ve compared a number of db records and have been unable to find a commonality between records that don’t show in the DAC.

Is this a bug, or is there logic that filters INLotSerialStatus records and is intended to hide them from the DAC based on certain criteria?

As far as I can tell, all the records that don’t show in the DAC, are records that have production orders that haven't been completed yet. However, there are plenty of records for items that are in various stages of production, that do show in the DAC.

icon

Best answer by kyoung14 3 August 2023, 20:38

View original

9 replies

Vinay Koppula
Rank
Userlevel 5
Badge +1

@MichaelShirk Is that a simple GI which fetches all the records, or you are filtering the data based on the dates and checking if the records match. 

Vinay K
Laura02
Rank
Badge +18

Hello @MichaelShirk ,

Is INLotSerialStatus the only DAC in the generic inquiry?  Or is it joined/related to another table? If there are multiple tables in your GI, please show us JOINS.  Incorrect type of join is a very common reason to see more or fewer records in the results than we expected.

Thank you!

 

Laura

Laura ~ Klear Systems, Inc. Irvine, CA. ~ Please remember to "Mark Best Answer" to help those who are facing the same problem.~
MichaelShirk
Rank
Userlevel 2
Badge

@Vinay Koppula @Laura02 
This issue persists even when INLotSerial status is the only DAC in the Generic Inquiry. In fact, this isn’t even a problem with the generic inquiry itself. As I mentioned in the original question, Even when I use the DAC schema browser to view the DAC source data, there are about 2,100 fewer records than there are in the same table in the database.

 

kyoung14
Rank
Userlevel 3
Badge

Any chance the extra records in the database table are for a different CompanyID?

MichaelShirk
Rank
Userlevel 2
Badge

@kyoung14  No, they’re all in the same company.

kyoung14
Rank
Userlevel 3
Badge

@MichaelShirk - You may know this already but just in case, the INLotSerialStatus DAC is combination of INLotSerialStatus table with an INNER JOIN to INItemLotSerial on InventoryID and LotSerialNbr.  So, if something is missing from INItemLotSerial table that could explain the discrepancy.  Not sure how INItemLotSerial could be missing a matching record but just something I thought I’d mention.  Otherwise if CompanyID is the same on every record in your SQL Query and every InventoryID/LotSerialNbr combo is present in each table then I’m out of ideas.  

RohitRattan88
Rank
Userlevel 7
Badge +4

@MichaelShirk 

Is it a multi tenant setup?

MichaelShirk
Rank
Userlevel 2
Badge

@kyoung14 
Ah, that’s the part I missed! I was fooled by the fact that the DAC had the same name as the DB table and failed to see that INLotSerialStatus is a projection.

Now I’m confused as to when that INItemLotSerial Record is generated, because sometimes records are created when the production order is created, and other times, not until production is completed.

My goal here, is a generic inquiry that shows all serialized items, regardless of whether or not production has been completed. I can’t base this on AMProdItem because when we first implemented Acumatica, there was quite a bit of inventory that was imported via Inventory Receipt so there are no production orders for those items. 

This may require a custom DAC.

kyoung14
Rank
Userlevel 3
Badge

@MichaelShirk - I haven’t done much in Acumatica around manufacturing/production but does seem odd there would be records in one but not the other - but there must be a reason (maybe?).  However, I really don’t know how those DACs are used.

 

I agree a custom DAC would seem to solve your problem.  Good luck!

 

And sorry before when I asked about CompanyID that I didn’t notice you had already filtered for just CompanyID = 2.

Reply


Terms & ConditionsCookie settings
About Acumatica ERP system
Acumatica Cloud ERP provides the best business management solution for transforming your company to thrive in the new digital economy. Built on a future-proof platform with open architecture for rapid integrations, scalability, and ease of use, Acumatica delivers unparalleled value to small and midmarket organizations. Connected Business. Delivered.
© 2008 — 2024  Acumatica, Inc. All rights reserved