Hello everyone,
Is there any way to put two rows into one line? Rather than it showing as separate rows.
Here is an example.
SC5225A Shop2OnHand has 19.00 but WrksOnHand has 0.00 I should just like to show one line with both of that information in the line. 
Here is the BAQ
TEAGLE-ProductionPlanMaterials.baq (27.5 KB)
Kind regards,
Aaron.
If all the fields were identical the GROUP BY would limit to one row.
But your fields Shortage, Shop2OnHand and and OnHandQty differ so theyād stay as two rows
I briefly looked at your BAQ and suspect that you have mutliple Releases per your sales order lines?
To prove this, add the OrderRel table to your top level query and display the release numbers along with the warehouse.
And/or - PART PLANT subquery looks like the root of your problem - you could try adding the Sales Order LINE to that too?
Row multiplication resulting from mixing ādimensionsā is something I see users doing all the time in Epicor. I usually start out I display header, line and releases in the raw data, then decide how to aggregate from there.
Hi Bruce,
I changed the original thread, please advise on the new one.
I have resolved the CheckWeights one.
Hi Calvin
Is there anyway to put two ones into one?
Calculated field etc?
Why are the two rows different?
Why does the 2nd one of the same JonNum and MtlSeq have a different OnHandQty? (Iām assuming thatās the OHQ of part SC5225A
Indeed it is, itās two different warehouses.
One single part as you say but pulling two different warehouse information.
Not sure I understand what you meanā¦ if you want to pivot values from a single column on multiple rows into a multivalue field on a single row
Otherwise, you may need to supply more details, raw data and what the end view should look like.
Can you combine the warehouses first?
Make a subquery that has the PartPlant table, and use calc fields to sum up the warehouses
Display fields would be:
SUM(OnHandQty)
Then use that sybquery in place of the PartPlpant table in your main query
I did think about using XML Path to produce this information. I discuss it would have to be comma split.
Combining the two QTYs wouldnāt work as the shopfloor require to know how much exactly is in each store area.
Can you upload an example of the solution you suggested Iām getting GROUP BY errors.
Kind regards,
Aaron.
A query that has any rows as Group By must have all non-aggregated rows as GROUP BY. Aggregated rows would be ones with functions like SUM() or COUNT() in them. Iāll see if I can whip up an example.
What happens if you just wrap your two fields in a sum and group the other fields? You have to remove the warehouse code for grouping but you have that displayed in your column.
See attached for example.
TEAGLE-ProductionPlanMaterialsEdit.baq (27.5 KB)
EDIT:
You will also need to remove the PartWhse.OnHandQty from this query. I didnāt do that.
Ross
@aarong - I donāt have multiple warehouses, so my example uses PartBin instead of PartPlant to get multiple records.
With a simple BAQ (single query) I get:
There are multiple rows for Mtl 10, because there are multiple PartBin records for that part. Just like you have multiple Warehouses.
Before I get too far into it, are Wrks and Shop2 the names of the warehouses?
Yes, theyāre names of warehouses.
i.e. parts may be in many bins.
Rather than digging into the specific query, here is a marked up schema screen shot - where I try to display some causes for row multiplication - and pseudo subquery for Part Bin Qtys.
Also, do you have you have access to EpicWeb/EpicCare sites?
And have already downloaded the Epicor ICE - āTools User Guideā and āCustomization User Guideā.
Can be kind of a pain to go thru and read butā¦ they do include some good BAQ examples, will show some grouping and aggregate calculations.
@aarong - I made a subquery containg just the PartBin table, with no table criterial, and the following display fields:
One Calc field for each warehouse, with the formulas:
SUM(case when PartBin.WarehouseCode = 'mfg' THEN PartBin.OnhandQty ELSE 0 end)
and
SUM(case when PartBin.WarehouseCode = 'CHAL2' THEN PartBin.OnhandQty ELSE 0 end)
The difference being which warehouse (mfg or CHAL2).
Then used that in place of the PartBin table in the top query:
with display fields:
The last two ācalc fieldsā are actually just āfieldsā from the subquery2. No calculation exists in Subquery1 (the top level query) for those two. The two Calc above it do use fields from JobMtl and Subquery2.
This combines all the part bins for all warehouses into a single record, with individual columns for the warehouse QOH
Hello @ckrusen,
I am trying to follow what you are doing here but the numbers arenāt making sense to me.
In your original BAQ(Post # 15 I think), do you have more than 2 warehouses the numbers are pulling from?
If you filter your original BAQ to use only those 2 warehouses (mfg and Chal2), do you get different numbers?
I appreciate the walkthrough you did, it will be very helpful in the future.
Thanks,
Shawn
In post 15, I was mistaken about only having one warehouse - Our production DB has just one per site, but our Test environment has multiple per site. Each row in the screenshot in post 15 was from a different warehouse. The warehouse names werenāt shown.
If i filtered my original BAQ (the one used for post 15) to just be warehouses mfg and CHAL2, then there would have only been 2 of the 6 rows shown.
Post 15 was mostly to show why one would get multiple rows.
After learning that the columns in the Original Posterās screen shot were for specifc warehouses, I made the sub query so that it returned one row per partnum.
