By using this website, you agree to our Terms of Use (click here)
Client is creating projects with stock items in the Cost Budget. In a GI, trying to link the items called for in the project to on hand quantities so user knows if they've acquired all needed items. Item data for project stored in PMCostBudget. On hand quantities in a couple different places but I am using IN.StoragePlaceStatus. Our inventory location for a project has the exact same name as the Project ID. Project ID PR00005, inventory location PR00005. Doing a LEFT JOIN on Project ID = Location and Inventory ID = Inventory ID seemed to be simple. However, in PMCostBudget, it does not contain the Project ID PR00005, instead it contains the database ID 3269 (or whatever). While I can link to PMCostBudget to PMProject to get the Project ID I want, that doesn't help me as I need to go from PMCostBudget to StoragePlaceStatus. I know in SQL I could create a view to do what I need but not in GI standard tools. Am I missing something or is this a case where I would need a custom view added to Acumatica that has theProject ID I need along with PMCostBudget data that I can then join to StoragePlaceStatus in a GI?
I'm thinking I need a custom view but I'm new to Acumatica and GI's so making sure I'm not missing something.
Hi @kevin-young
You can definitely create a relation in GI to do this. You need to have all 3 tables added to the GI. In your relations, start with PMCostBudget and create the relation to PMProject on the int based ID. You can then create the relation between PMProject and StoragePlaceStatus based on the string based project code.
Thanks for the feedback. But I need to left join to StoragePlace Status on Project code AND Inventory ID. Inventory ID is only in PMCostbudget.
@kevin-young
In your second join between PMProject and StoragePlaceStatus, you can use:
=[PMCostBudget.InventoryID]
Assuming you didn't change the alias of your table, that should do the trick.
I thought I tried that before but I will try that again. Thanks!
Thanks Wyatt.ERP. I did try that before but you reminding me of that forced me to look at it again and I realized I was linking to the wrong column when I tried before. Appears to work just as I need now!!
Online Members
