Skip to main content
Solved

Unable to access all database records through a Generic Inquiry


MichaelShirk
Captain I
Forum|alt.badge.img+1

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.

Best answer by kyoung14

@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.  

View original
Did this topic help you find an answer to your question?

10 replies

Vinay Koppula
Semi-Pro II
Forum|alt.badge.img+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. 


Laura02
Captain II
Forum|alt.badge.img+19
  • Captain II
  • 3127 replies
  • August 3, 2023

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


MichaelShirk
Captain I
Forum|alt.badge.img+1
  • Author
  • Captain I
  • 103 replies
  • August 3, 2023

@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
Varsity I
Forum|alt.badge.img
  • Varsity I
  • 40 replies
  • August 3, 2023

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


MichaelShirk
Captain I
Forum|alt.badge.img+1
  • Author
  • Captain I
  • 103 replies
  • August 3, 2023

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


kyoung14
Varsity I
Forum|alt.badge.img
  • Varsity I
  • 40 replies
  • Answer
  • August 3, 2023

@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
Acumatica Moderator
Forum|alt.badge.img+4
  • Acumatica Moderator
  • 245 replies
  • August 3, 2023

@MichaelShirk 

Is it a multi tenant setup?


MichaelShirk
Captain I
Forum|alt.badge.img+1
  • Author
  • Captain I
  • 103 replies
  • August 3, 2023

@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
Varsity I
Forum|alt.badge.img
  • Varsity I
  • 40 replies
  • August 3, 2023

@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.


  • Freshman I
  • 1 reply
  • May 12, 2024

I am a newcomer to Acumatica and this post was helpful.  My question is where can I find the database schema or the DAC?  I see that you connected Microsoft SQL Server, can you share how you did that?  Was it ODBC or ODATA?  Thanks!

 


Reply


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings