Only add items in collection if it equal a specific value

[arturdjali]

Hi,

In this power app i create a CSV that captures all the expense items from a SharePoint list. What i want to do is if the expense has the ExpenseCategory of "Out of Pocket" then only add that item any other ExpenseCategory then shouldn't be added to the collection.

this what my formula currently looks like.

Clear(exportSVCol);
ForAll(
    AddColumns(
        colExportToCSVTCExpense As ThisExportToTCExpense,
        "Budgetcode",
        Filter(
            colExportToCSVTCBudgegcode,
            TARef = ThisExportToTCExpense.TARef
        ),
        "Travel",
        LookUp(
            ColExportToCSV,
            ThisExportToTCExpense.TARef = Text(ID)
        ),
        "VendorsID",
        LookUp(
            colExportToCSVVendorID,
            EmailAddress = LookUp(
                ColExportToCSV,
                ThisExportToTCExpense.TARef = Text(ID)
            ).Email_x0020_Address
        ),
        "GLCodes",
        LookUp(
            'GL Accounts',
            Title = ThisExportToTCExpense.ExpenseType
        )
    ) As ThisExpense,
    Collect(
        exportSVCol,
        {
            VendorNo: ThisExpense.VendorsID.VendorID,
            PostDate: "",
            YourReference: ThisExpense.TravelAuthorisation.TANumber & "-" & ThisExpense.Travel.TravellerName,
            ExtDocNo: ThisExpense.TravelAuthorisation.TANumber,
            AcctNo: ThisExpense.GLCodes.GLNumber,
            Desc: ThisExpense.Travel.Title,
            InvoiceDesc: "",
            Qty: "1",
            UnitCost: ThisExpense.SubTotal,
            SpeedKeyCode: Concat(
                ThisExpense.Budgetcode,
                Title & "; "
            ),
            Dimension1: "",
            Dimension2: "",
            Dimension3: "",
            Dimension4: "",
            Dimension5: "",
            Dimension6: "",
            Dimension7: "",
            Dimension8: "",
            PercentageSplit: Concat(
                ThisExpense.Budgetcode,
                Percentage & ", "
            )
        }
    )
);

 Thank you

[iAm_ManCat]

If you add a condition when you are collecting, you should be able to - I assume you have the Category column in your datasource:

Clear(exportSVCol);
ForAll(
    AddColumns(
        colExportToCSVTCExpense As ThisExportToTCExpense,
        "Budgetcode",
        Filter(
            colExportToCSVTCBudgegcode,
            TARef = ThisExportToTCExpense.TARef
        ),
        "Travel",
        LookUp(
            ColExportToCSV,
            ThisExportToTCExpense.TARef = Text(ID)
        ),
        "VendorsID",
        LookUp(
            colExportToCSVVendorID,
            EmailAddress = LookUp(
                ColExportToCSV,
                ThisExportToTCExpense.TARef = Text(ID)
            ).Email_x0020_Address
        ),
        "GLCodes",
        LookUp(
            'GL Accounts',
            Title = ThisExportToTCExpense.ExpenseType
        )
    ) As ThisExpense,
    If(
        ThisExpense.ExpenseCategory="Out of Pocket",
        Collect(
            exportSVCol,
            {
                VendorNo: ThisExpense.VendorsID.VendorID,
                PostDate: "",
                YourReference: ThisExpense.TravelAuthorisation.TANumber & "-" & ThisExpense.Travel.TravellerName,
                ExtDocNo: ThisExpense.TravelAuthorisation.TANumber,
                AcctNo: ThisExpense.GLCodes.GLNumber,
                Desc: ThisExpense.Travel.Title,
                InvoiceDesc: "",
                Qty: "1",
                UnitCost: ThisExpense.SubTotal,
                SpeedKeyCode: Concat(
                    ThisExpense.Budgetcode,
                    Title & "; "
                ),
                Dimension1: "",
                Dimension2: "",
                Dimension3: "",
                Dimension4: "",
                Dimension5: "",
                Dimension6: "",
                Dimension7: "",
                Dimension8: "",
                PercentageSplit: Concat(
                    ThisExpense.Budgetcode,
                    Percentage & ", "
                )
            }
        )
    )
);

[arturdjali]

Hi @iAm_ManCat,

i am having a little trouble getting this to work when multiple items are selected as even if the condition is not matched then for each selected item i would require the if not true option to run and populate a row in the collection with the required data. Any ideas on how i can go about this?

Clear(exportSVCol);
ForAll(
    AddColumns(
        colExportToCSVTCExpense As ThisExportToTCExpense,
        "Budgetcode",
        Filter(
            colExportToCSVTCBudgegcode,
            TARef = ThisExportToTCExpense.TARef
        ),
        "TravelAuthorisation",
        LookUp(
            ColExportToCSV,
            ThisExportToTCExpense.TARef = Text(ID)
        ),
        "VendorsID",
        LookUp(
            colExportToCSVVendorID,
            EmailAddress = LookUp(
                ColExportToCSV,
                ThisExportToTCExpense.TARef = Text(ID)
            ).Email_x0020_Address
        ),
        "GLCodes",
        LookUp(
            'GL Accounts',
            Title = ThisExportToTCExpense.ExpenseType
        ),
        "PerDiemCost",
        LookUp(
            colExportToCSVTCPerDiemDays,TARef = ThisExportToTCExpense.TARef
        )
    ) As ThisExpense,
        If(
        ThisExpense.CategoryType="Out of Pocket",
        Collect(
        exportSVCol,
        {
            VendorNo: ThisExpense.VendorsID.VendorID,
            PostDate: "",
            YourReference: "",
            ExtDocNo: ThisExpense.TravelAuthorisation.TANumber,
            AcctNo: ThisExpense.GLCodes.GLNumber,
            Desc: ThisExpense.TravelAuthorisation.TANumber & "-" & ThisExpense.TravelAuthorisation.Traveller_x0020_Name,
            InvoiceDesc: ThisExpense.TravelAuthorisation.TANumber & "-" & ThisExpense.TravelAuthorisation.Traveller_x0020_Name,
            Qty: "1",
            UnitCost: ThisExpense.SubTotal,
            SpeedKeyCode: Concat(
                ThisExpense.Budgetcode,
                Title & "; "
            ),
            Dimension1: "",
            Dimension2: "",
            Dimension3: "",
            Dimension4: "",
            Dimension5: "",
            Dimension6: "",
            Dimension7: "",
            Dimension8: "",
            PercentageSplit: Concat(
                ThisExpense.Budgetcode,
                Percentage & ", "
            ),
            Currency:"USD"
        },  
        {
           VendorNo: ThisExpense.VendorsID.VendorID,
            PostDate: "",
            YourReference: "",
            ExtDocNo: ThisExpense.TravelAuthorisation.TANumber,
            AcctNo: "55005",
            Desc: ThisExpense.TravelAuthorisation.TANumber & "-" & ThisExpense.TravelAuthorisation.Traveller_x0020_Name,
            InvoiceDesc: ThisExpense.TravelAuthorisation.TANumber & "-" & ThisExpense.TravelAuthorisation.Traveller_x0020_Name,
            Qty: "1",
            UnitCost: Sum(colExportToCSVTCPerDiemDays,SubTotal),
            SpeedKeyCode: Concat(
                ThisExpense.Budgetcode,
                Title & "; "
            ),
            Dimension1: "",
            Dimension2: "",
            Dimension3: "",
            Dimension4: "",
            Dimension5: "",
            Dimension6: "",
            Dimension7: "",
            Dimension8: "",
            PercentageSplit: Concat(
                ThisExpense.Budgetcode,
                Percentage & ", "
            ), 
            Currency:"USD"
        }
    ),
)
);
   ExportToCSV.Run(
    User().Email,
    ColMarkasPaidtTARef,
    JSON(
        exportSVCol,
        JSONFormat.IncludeBinaryData
    )
);
UpdateContext({varRestCheckbox: true});
UpdateContext({varRestCheckbox: false});
Clear(ColMarkasPaid);
Clear(ColExportToCSV);
Clear(colExportToCSVTCBudgegcode);
Clear(colExportToCSVTCExpense);
Clear(colExportToCSVVendorID);
Clear(colExportToCSVTCPerDiemDays);