SAP Build Challenge – Week 1 – Formulas

[Dan_Wroblewski]

This post is part of the SAP Build Community Challenge (April 2023)

This week’s challenge involves formulas – a key feature of SAP Build Apps. Formulas are used in bindings of components, and enable you to manipulate data before it is displayed in a component or before it is sent to a data source.

Formulas are created in the formula editor, which provides some tools for making writing formulas easier:

• It has a list of functions, which can be added to the formula with a click.
• It has documentation on each function, plus examples.
• It has a live testing of functions so you can be certain how they work.
• It checks the syntax of the formula.
• It shows an expected sample result of the function.
• It checks the resulting data type against the data type expected by the component or flow function using the formula.

Here are some learning materials about formulas:

• Formulas (documentation)
• Formula Functions (reference)
• No-code App Programming 101 (video by Akseli Virtanen)

Setup

We will give you an app that looks like this (I like cats).

To set up the challenge, do the following:

1. Download the skeleton project called Challenge - Formulas.zip.gpg.
2. Create a new SAP Build Apps project in your tenant or the sandbox.
3. Import the downloaded file into your new project by selecting History > Replace in the top-right corner, and then selecting the downloaded project. 

You should now have the skeleton project.

Inside the app, we have already created a page variable called StartValue that contains the starting data that we want you to transform. 

The Challenge

When you are ready, on the UI Canvas, select the Transform button, open the logic canvas, then select the Set page variable flow function, and finally go to set the formula for Assigned Value on the right (which currently returns an empty list).

This is what you need to do:

1. Write a formula that replaces the empty list for the assigned value and that does the following:
   • Aggregates the data from StartValue so that in EndValue each name appears only once with an average calculated from the amount fields.
   • Names should be sorted alphabetically, from A to Z
   • Averages should be rounded to the nearest integer.
2. Launch your app.
3. Click the Transform button (which will display 4 names and an average for each), and then type your name in the input field.
4. Take a screenshot and post it to THIS discussion.
   • BONUS: Add to your entry the movie from where the 4 names in the results are taken.

Your screenshot should look something like this but without the blur (I need to see the names and averages) and with your name:

Good luck ... and have fun!!

Hint

You have to do 3 things in the formula (aggregate/group, sort, round), so find a function that does each one. Use the wonderful editor features that document the available functions.

For example, for aggregating, go to the List area, and then find a function that aggregates (in this case, GROUP). You'll see all the documentation for it on the right, plus a little playground inside the documentation for testing it. Pretty cool! 

 

[VijayCR]

Hi @Dan_Wroblewski  ,

Very Interesting Challenge great to learn the formulae features and the movie name is Madagascar .

Thanks,

Vijay

[Chetan_Jadhav]

Hi Dan,
Completed First Challenge. It was Great.
Movie Madagascar.

[Dan_Wroblewski]

On to the second one now - https://groups.community.sap.com/t5/sap-builders-discussions/sap-build-challenge-week-2-ui-design/td...

And https://www.youtube.com/watch?v=uq2wJNqygkU

[saranraj_9021]

Thanks for the challenge Dan, 

Better Late than never !! 

Here is the screenshot of the App and of course the Bonus, the names are from Madagascar movie 

[Dan_Wroblewski]

Not late at all ... you have to end of month to do any and all the challenges 😺

[SubaR]

Hi Dan ,

Thanks for the challenge. Here is the screenshot and the movie is Madagascar.

Had fun learning about formulas!

[Kashish_Rajpal]

Hi Dan,

Thank you for the challenge. A bit tricky and interesting though 🙂
Here's my screenshot for the challenge and movie is Madagascar.

[Dan_Wroblewski]

Great! 😺

We didn't want it to be too easy

[LeandroRibeiro]

Hello Daniel,

Great idea to create these challenges.
It really forced me to study the docs and research how to use the functions used.
If it's not too much to ask, feel free to create new formula challenges (besides, of course, the others to come)
Thank you.
Best regards from Brazil.

[Dan_Wroblewski]

Weekly challenges, including or especially for formulas, is an interesting and fun idea. Thanks for participating.

[nishantbansal91]

Sorry for late @Dan_Wroblewski 

This is the output

[Dan_Wroblewski]

Not late at all -- you have till May 7 for all of them 😺

[PriyanjanaRoy]

My first time trying anything with Build Apps 😞 I couldn't get the numbers! , hence the sad smiley in the image. Anyway i can get some help on this? 
Also the Movie is Madagascar ! Have to rewatch now 

[Dan_Wroblewski]

I didn't mean to make you sad 😐 Share your formula and we can work together. You obviously got the grouping part OK.

[PriyanjanaRoy]

I had used this formula for the grouping - 

GROUP(pageVars.StartValue, item.name, {name:key,actors:items})

and sorting then 

SORT_BY_KEY((GROUP(pageVars.StartValue,item.name,{name:key,actors:items})),"name","asc")

[Dan_Wroblewski]

Great ... you've grouped the data but you haven't created a single average. So instead of "actors : items" (which just creates the same list of amounts, you want a field called "average" that is equal to a number that is the average of all those items. 

Luckily, you have all the items to average -- in the items object -- but you have to use the functions available to take all that data and turn it into a single number. Obviously, AVERAGE will be involved somehow, and in order to use AVERAGE you need a list of values. Consider using PLUCK to get that list from items.

[PriyanjanaRoy]

I tried to concat the average from the item . Not sure where I am going wrong 😞 
SORT_BY_KEY((GROUP(pageVars.StartValue,item.name +" "+ AVERAGE(PLUCK(item,"amount")),{name:key,average:items})),"name","asc")

[Dan_Wroblewski]

SORT_BY_KEY((GROUP(pageVars.StartValue,item.name +" "+ AVERAGE(PLUCK(item,"amount")),{name:key,average:items})),"name","asc")

You have to Group the items, so you need something like this:

GROUP(pageVars.StartValue, item.name, {Some object}) 

This creates a list with one object per group (shown as pseudocode above as "Some object", but what do you want that object to be?

You want that object to have the name of the group and then an average, something like this:

{ "name" : "Marty", "average" : 12.2}

The first part you pretty much have, so it is like this:

{name: key, average: 10.11}

But of course you can't hardcode the value, you have to calculate it from the "items" list you get as an object as part of the GROUP function. In order to use the AVERAGE function, you need a simple list of numbers, so you need to convert the "items" object into the simple list, and this you use the PLUCK function.

So in the end you get:

GROUP(pageVars.StartValue, item.name, {name: key, average: ROUND(AVERAGE(PLUCK(items,"amount")), 0) })

I added a ROUND function – part of requirement – which takes an extra parameter 0 to indicate to round it to nearest whole number.

The only thing left is to sort it, which you have already started with above with the SORT_BY_KEY function.

[PriyanjanaRoy]

Thank you for virtually solving this for me and helping me understand where I was going wrong. I did try to add this part - AVERAGE(PLUCK(item,"amount")) with a value to "average" but did not consider items as the object to be used ! Thank you 

[ec1]

Great challenge as always.

The movie is Madagascar. 

S

[Vishal_97]

Hi @Dan_Wroblewski ,

Thanks for the interesting challenge. The Movie name is Madagascar!

[govardhansahil]

Thanks for the challenge. It is great way to learn and get familiar with the tools. Looking forward for upcoming challenges.

[Tomislav]

Hi @Dan_Wroblewski ,

Thanks for the challenge, i managed to erase StartValue data at first and spent some long minutes wondering what kind of magic was bringing data in the app... and then i reimported everything.

The Movie name is Madagascar!

[former_member136915]

Hello Dan,

Movie is Madagascar.

Inspiring SAP Build Challenge.

Thanks.

[Dan_Wroblewski]

Thanks for participating 😺

[evra83]

Hi Dan,

Thanks for this challenge

[Dan_Wroblewski]

Can you show your formula?

[Ranjith_Kumar]

Hi Dan, 

Thanks for the challenge . late to the trend..;)

Movie name: Madagascar

[PrashantJayaram]

Hi Daniel, please see above.  And the movie is Madagascar.

[HorstSchaude]

Finally 😉

[Dan_Wroblewski]

Thanks for entering and keeping at it until it was complete 😺

[Vitaliy-R]

You ruined my Friday...

Personally, I prefer Skipper, Kowalski (a friend of Wroblewski?), Rico and Private 🐧 

[Dan_Wroblewski]

Sorry about that. Yes, I should have used the penguins, or the monkeys

[Sharadha1]

I am joining a little late to the party.. Nevertheless, here is my entry for this challenge.

Movie: Madagascar - I like to move it, move it!! 

 Looking forward to crack the others too..

-Sharadha

[Dan_Wroblewski]

Not late at all ... still another 3 weeks for all the challenges ... hope you continue with the others 😺

[advitramesh]

Hello Dan

I get the below error when I try to import the project on my AppGyver platform. Is the project only compatible with SAP build?

Thanks for your time

Regards

Advit

[Dan_Wroblewski]

I believe you can export from SAP Build and import to AppGyver community edition.

But you can quickly sign up for the sandbox system to do the challenges: https://groups.community.sap.com/t5/sap-builders-blog-posts/announcing-the-sap-build-apps-sandbox/ba... ... let me know if that works for you

[rudavi]

Hello Dan,

Sorry for being late,  but I discovered this challenge just yesterday

Thanks for this challenge!!

Please see the screenshot below:

[Dan_Wroblewski]

Thanks for joining ... but the data above is not correct ... what happened? You were supposed to use the data already inside the app 

[rudavi]

Sorry, I was thinking about switching the data to something I liked best.