I am using a Set Argument/Variable widget and need an expression that returns the OnHand Quantity of the PartWarehouse table. In this widget I have a temp table called ttJobMtl I somehow need to link it to the PartWarehouse table by company by part and get the onhand quantity. Any help is appreciated.
Checkout the “simple one liners” in the following:
https://www.epiusers.help/t/simple-one-liner-c-to-retrieve-data-from-a-table/44627/2
You should be able to tweak that to your needs.
something like:
Db.PartWhse.Where( r =>r.Company == callContextClient.CurrentCompany && r.PartNum == ttJobMtlRow.PartNum).Select( r =>r.OnHandQty).DefaultIfEmpty(-1).FirstOrDefault()
If ttJobMtl has multiple records, you might need to add constraints for the AssemblySeq and MtlSeq to the WHERE clause
This keeps giving me a warning of expression should only contain one statement
Make sure there’s no trailing semicolon (it’s habit to add them).
I get the following to pass a syntax check:
The variable you’re assigning it to is of type Decimal?
try removing .DefaultIfEmpty(-1) That is just there to give you a value (instead of null) if no records are found.
the -1 might look like an integer, when it is expecting a decimal.
So I have used the suggestion by Calvin and it does work. Because I am incompetent in writing code I am going to write this out in my language to see if you generous people can help me out. Once I have something that works I have quite a few places I can use and modify as needed. Thanks in Advance
The BPM I am working in is on Job Entry. Based on Calvins suggestion to ensure I only have one record this is how I visualize it.
I have a table called ttJobMtl. I need to limit that to only part numbers that begin with TT
Then I need to link to the PartWhse and limit by warehouse TTMAIN
These tables would be joined by Company and PartNum
This code does check okay but I am not sure how to do the limitations.
Db.PartWhse.Where( r =>r.Company == callContextClient.CurrentCompany && r.PartNum == ttJobMtlRow.PartNum).Select( r =>r.OnHandQty).DefaultIfEmpty(-1).FirstOrDefault()
I’m not even sure if the limitations are needed. I can never keep track of when a BPM method works on multiple rows at a time.
It might help to layout what is going on in that code…
The .DefaultIfEmpty(..) is optional, and can be used to set the value returned if, no matching rows are found.
The .FirstOrDefault() instructs it to return the value from the first row (if multiple rows are found)
So you want to add to the WHERE part.
Try making it:
.Where(
r =>r.Company == callContextClient.CurrentCompany
&& r.PartNum == ttJobMtlRow.PartNum
&& r.PartNum.StartsWith("TT")
&& r.WarehouseCode == "TTMAIN"
)
edit
FWIW - hard coding values like “TT” and “TTMAIN” is generally bad practice (but gets the job done).
1 Like
So I would like to limit the PartWarehouse to only parts beginning with TT but not sure on the syntax. Searching to see if I can figure it out.
The r in the expression represents the record from the table (PartWhse in your case)
So the line highlighted above limits the records to look at to ones where PartWhse.PartNum starts with “TT”.
So I think I may have it but wanted you to verify.
Okay so that got it to work and hopefully gave me enough to work with. Turns out the ttJobMtl temp table has no records in it so I guess I will have to use the ttJobhead and join to the jobMtl then to the Part Warehouse unless there is a suggestion of a better way.
So here is what I have now.
Db.PartWhse.Where( r =>r.Company == callContextClient.CurrentCompany && r.PartNum == ttJobMtlRow.PartNum && r.PartNum.StartsWith(“TT”) && r.WarehouseCode == “TTMAIN”).Select( r =>r.OnHandQty).DefaultIfEmpty(-1).FirstOrDefault()
So are there any examples of joining 3 tables together before I start trying to figure this out. This has been very helpful.
I’m not following what you’re trying to do.
You are so kind in helping. Thank you so much. Let me back up and give you the end goal.
When a user changes the “Make to Stock” quantity in Job Entry we are trying to write a BPM that will find the associated part in JobMtl that starts with a “TT” (as there will only be one) and multiply the Quantity/Per by the value entered to see how much we need. Then we compare that value to the value in PartWhse specific to Warehouse “TTMAIN” to see if there is enough and return a message if there is not.
In testing since 7:30am this morning I have just found the ttJobMtl table has no records in it but the db.JobMtl does. I was working on Setting some Variables in the BPM so I could get the values of How much we need and another of how much we have so that we can compare and determine if we need to notify there is not enough quantity.
This has been a long day trying and nothing to show at the end of 8 hours but still determined to deliver. I figure the more I work with it the more I will learn and become better.
Well I tried this on my own and obviously didn’t work. Feeling very defeated.
Db.PartWhse.Where( r =>r.Company == callContextClient.CurrentCompany && ttJobHead.Company == callContextClient.CurrentCompany && Db.JobMtl.Company == callContextClient.CurrentCompany && ttJobHeadRow.jobNum == Db.JobMtl.JobNum && r.PartNum == Db.JobMtl.PartNum && Db.JobMtl.PartNum.StartsWith(“TT”) && r.PartNum.StartsWith(“TT”) && r.WarehouseCode == “TTMAIN”).Select( r =>r.OnHandQty).DefaultIfEmpty(-1).FirstOrDefault()
Well With the help of Calvin I was able to get enough code to modify and make it work. I ended up using a lot of variables to pull the information I needed and then do a calculation.
Thanks for the help. This ticket can be closed.
1 Like