AugForums.com

An Acumatica User Group

  • Free
    • Start Here
    • In-Person Gatherings
    • Power BI Workshop
    • Podcast
    • Rolodex
    • Blog
    • Forums
  • Paid
    • AugSQL
    • GI Course
    • GI Library
    • Consulting
  • Register
Acumatica Forums

By using this website, you agree to our Terms of Use (click here)

Forums
AUG Forums
Acumatica Generic I...
GI to compare FIFO ...
 
Notifications
Clear all

Questions GI to compare FIFO last cost to stock item last cost

 
Acumatica Generic Inquiries & Pivot Tables
Last Post by Travis 2 years ago
2 Posts
1 Users
1 Reactions
1,412 Views
RSS
Travis
Posts: 41
 Travis
Topic starter
March 13, 2024 6:30 pm
(@travisl)
Trusted Member
Joined: 3 years ago

Struggling with this one.  We are using a daily scheduled business event with this GI to update the last cost field on stock items.  We use FIFO and need to update that stock item field with the first (oldest) cost layer. 

I used some creative engineering with the joins of the same table (Thanks Tim?).

We use the InCostStatus to InCostStatusPrev relationship to ensure we are getting the first cost of the FIFO layer.  We pull all of the costs for the FIFO tier and get the item cost by using TotalCost / QtyOnHand - this is the last cost we use.  We also group by InventoryID otherwise it duplicates the line for each cost layer.  When we do that the [InItem.lastCost] field adds the last cost to itself for each line so $10 turns into $30 if there are 3 cost layers.

What we want to do is limit the GI to only update if the [TotalCost / QtyOnHand] is different than what the [InItem.LastCost] field is so we aren't importing 2,000 items every night when it isn't necessary.

I've had no luck in doing so.  Any help?

 

GI is attached:

Last FIFO Cost.xml

 

Screenshot 2024 03 13 at 19 04 44 WP   Last FIFO Cost

 

Screenshot 2024 03 13 at 19 05 08 Generic Inquiry
Screenshot 2024 03 13 at 19 05 43 Generic Inquiry
Screenshot 2024 03 13 at 19 06 02 Generic Inquiry

 


Topic Tags
Generic Inquiry
1 Reply
Travis
Posts: 41
 Travis
Topic starter
March 14, 2024 11:00 pm
(@travisl)
Trusted Member
Joined: 3 years ago

I think I have it.  I forgot to round the 2nd condition for LastCost by 4 digits so it was not matching.  The math was going out to 6 or 7 digits so it obviously wasn't equal to the Stock items last cost which was rounded to 4 digits.


Reply
Tim Rodman reacted
Forum Jump:
  Previous Topic
Next Topic  
Related Topics
  • Unable to Add Field "Unapplied Balance" to Generic Inquiry "Payments and Applications"
    10 months ago
  • Equivalent to Excel Post-Special, "transpose"
    2 years ago
  • Generic Inquiry - Last Modified On Date Change
    2 years ago
  • Generic Inquiry, pulling data from two unrelated sources
    2 years ago
  • Adding Override Contact Info to GI Results
    2 years ago
Topic Tags:  Generic Inquiry (41) ,
Forum Information
Recent Posts
Unread Posts
Tags
  • 12 Forums
  • 2,528 Topics
  • 10.9 K Posts
  • 15 Online
  • 2,412 Members
Our newest member: Peter Paasch
Latest Post: Credit Reference Report Needed
Forum Icons: Forum contains no unread posts Forum contains unread posts
Topic Icons: Not Replied Replied Active Hot Sticky Unapproved Solved Private Closed

Online Members

 No online members at the moment

Acumatica Forums

Terms of Use & Disclaimers :: Privacy Policy

Copyright © 2026 · AUG Forums, LLC. All rights reserved. This website is not owned, affiliated with, or endorsed by Acumatica, Inc.

‹›×

    ‹›×