cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
arturdjali
Helper I
Helper I

Only add items in collection if it equal a specific value

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

2 REPLIES 2
iAm_ManCat
Super User
Super User

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 & ", "
                )
            }
        )
    )
);

 

@iAm_ManCat


Please 'Mark as Solution' if someone's post answered your question and always 'Thumbs Up' the posts you like or that helped you!


Thanks!
You and everyone else in the community make it the awesome and welcoming place it is, keep your questions coming and make sure to 'like' anything that makes you 'Appy
Sancho Harker, MVP


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);

 

 

Helpful resources

Announcements
Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

365 EduCon 768x460.png

Microsoft 365 EduCon

Join us for two optional days of workshops and a 3-day conference, you can choose from over 130 sessions in multiple tracks and 25 workshops.

Users online (2,971)