I am using PXResult in foreach and it gives single row was requested error occured
I am using PXResult in foreach and it gives single row was requested error occured
Best answer by chameera71
The error you're encountering, "Single row was requested but the resultset returned multiple rows", typically occurs when you're trying to retrieve a single row from the database, but the query returns multiple rows, causing an issue with how the result is handled.
In your case, you are using PXSelect<>
to fetch multiple rows, but you are attempting to loop through the results using a foreach
statement. However, it appears that there may be a mismatch between how the result set is being handled and how many rows are actually being returned by your query.
Use PXResultset<>
in the foreach
Loop: When using PXSelect<>
to fetch multiple rows, you need to loop through the result set using PXResultset<>
, which is already defined in your variable materials
.
But you are directly trying to use the type EWPMBOMMat1
in the foreach
loop, which causes the error. Instead, the foreach
should loop over the PXResult<>
type.
Here's how you can update your code:
csharp
private decimal CalculateFormulaMaterialCost() { PXResultset<EWPMBOMMat1> materials; // condition applied on 04-05-2023 #98977 //added && for full FG BOM 08-05-2023 if (EWPMBOMUtils.IsFullFGBOM(Bom)) { materials = PXSelect<EWPMBOMMat1, Where<EWPMBOMMat1.bomID, Equal<Required<EWPMBOMMat1.bomID>>, And<EWPMBOMMat1.revisionNo, Equal<Required<EWPMBOMMat1.revisionNo>>, And<EWPMBOMMat1.bomCD, Equal<Required<EWPMBOMMat1.bomCD>>, And<Where<EWPMBOMMat1.sourceType, Equal<EWPMItemSourceType.IntermediateSourceType>>>>>>> .Select(PXGraph, Bom.BomID, Bom.RevisionNo, Bom.BomCD); } else { materials = PXSelect<EWPMBOMMat1, Where<EWPMBOMMat1.bomID, Equal<Required<EWPMBOMMat1.bomID>>, And<EWPMBOMMat1.revisionNo, Equal<Required<EWPMBOMMat1.revisionNo>>, And<EWPMBOMMat1.bomCD, Equal<Required<EWPMBOMMat1.bomCD>>, And<Where<EWPMBOMMat1.sourceType, Equal<EWPMItemSourceType.FormulaSourceType>, Or<EWPMBOMMat1.sourceType, Equal<EWPMItemSourceType.RoutingSourceType>>>>>>> .Select(PXGraph, Bom.BomID, Bom.RevisionNo, Bom.BomCD); } decimal totalMaterialCost = 0; // Use PXResultset here foreach (PXResult<EWPMBOMMat1> result in materials) { EWPMBOMMat1 material = result; material.PlanCost = material.UnitCost * material.BaseQtyReq; totalMaterialCost += material.PlanCost ?? 0; } return totalMaterialCost; }
PXResultset<>
vs PXResult<>
:
PXResultset<>
is used for handling a set of rows returned by the PXSelect<>
query.PXResult<>
is used when iterating over multiple rows in the PXResultset<>
.foreach
loop: You need to iterate over PXResult<EWPMBOMMat1>
instead of EWPMBOMMat1
. This ensures that the PXResultset
properly handles multiple rows without generating the error.
materials
variable always contains rows before the foreach
loop is executed. You can add a check like: csharp
if (materials == null || materials.Count == 0) { return 0; // or handle empty material set }
With this approach, you should be able to eliminate the "Single row requested" error and successfully iterate through the material records for cost calculation.
😀💡
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.