cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Hector999-0
Regular Visitor

I need your assistance in developing a formula for a cubicle bidding process.

I'm trying to develop a formula to calculate the Awarded Cubicle for each employee in my organization based on the Employee’s cubicle selections.  The bidding process for my organization will be based on the dummy data I provided in Excel format, which consist of several columns: Employee, Floor, Division, Priority, Hire Date, SA (Seat or Cubicle Awarded), and Selection1...Selection20.  The data will be sorted by the Seniority ranking (Hire Date) and Priority groups. The formula must start by creating the Employee collection.  Then formula must assign the Employee A's 1st Selection ("5208") to the SA column because this employee has the highest seniority. The formula must create the Awarded collection to track the awarded cubicles (or selections).  So, the "5208" selection must be added to the Awarded-Award column. At this point the formula must loop through the Employee collection staring at the 2nd row. The formula must have a condition to test all selections1 – 20 to see if a selection has been awarded already. At this point, the For All is pointing to row 2 (Employee B’s Selection1 (“5212”). The condition must perform the following test: Is Selection1 (“5212”) in the Awarded collection?  If “Yes”, read next Selection2 and test again. If “NO”, assign selection “5212” to the SA column of 2nd row in the Employee collection and to the Awarded collection (Awarded-Award column). Next read the Employee's 3rd row and continue testing. Can this formula de developed in Formula language? OR Do I need to use another programming language to do this logic? Your help is appreciated.  Thank you. Note: The attached image and file contains a small portion of the data for testing purposes

4 REPLIES 4
Carlosr
Microsoft
Microsoft

Is this a triggered calculation?  I assume this will be determined on a daily basis.

 

I recommend doing this in Power Automate.  It will be easier to test and maintain.  Such a complex PowerFX formula may be possible but will require some juggling.

StalinPonnusamy
Super User
Super User

Hi @Hector999-0 

 

As Carlos mentioned this can be possible in Power Automate.

 

Another option is to have a custom API or Azure function for complicated formulas and calculations.

I got no solution.

Here is the solution 

ClearCollect(BetaColl,{A: 2786,B: 2334,C: 2786},{A: 2675,B: 2098,C: 2566},{A: 2222,B: 2787,C: 2777},{A: 2334,B: 2999,C: 2332},{A: 2222,B: 2786,C: 2334});

//comparison collection

ClearCollect(AlphaColl,{selected: First(BetaColl).A});

//helper collection, -1 is a placeholder that gets ignored

ClearCollect(GammaColl,{a: -1});

ForAll(

    LastN(BetaColl,CountRows(BetaColl) - 1),

    ForAll(

        Split(Substitute(Substitute(JSON(ThisRecord),"{",""),"}",""),","),

        Collect(GammaColl,{a: Value(Last(Split(Result,":")).Result)});

    );

 

    If(

        Not(First(LastN(GammaColl,3)).a in AlphaColl.selected),

        Collect(AlphaColl, {selected :Value(First(LastN(GammaColl,3)).a)}),

        //else if

        Not(First(LastN(GammaColl,2)).a in AlphaColl.selected),

        Collect(AlphaColl, {selected : Value(First(LastN(GammaColl,2)).a)}),

        //else if

        Not(Last(GammaColl).a in AlphaColl.selected),

        Collect(AlphaColl, {selected : Value(Last(GammaColl).a)})

    )

)

 

Helpful resources

Announcements
October Events

Mark Your Calendars

So many events that are happening this month - don't miss out!

Ignite 2022

WHAT’S NEXT AT MICROSOFT IGNITE 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Power Apps Africa Challenge 2022

Power Apps Africa Challenge

Your chance to join an engaging competition of Power Platform enthusiasts.

Top Solution Authors
Top Kudoed Authors
Users online (3,432)