SAP Builders Discussions
Join the discussion -- ask questions and discuss how you and fellow SAP Builders are using SAP Build, SAP Build Apps, SAP Build Process Automation, and SAP Build Work Zone.
cancel
Showing results for 
Search instead for 
Did you mean: 

April (Citizen) Developer Challenge – SAP Build Apps: Task 2 - Formulas

Dan_Wroblewski
Developer Advocate
Developer Advocate
6,147

The challenge is now over. See our wrap-up blog.

2023-12-24_18-40-33.png

Now the challenge gets real 😯

I’ve heard that many developers – citizen and otherwise -- pretty much understand the idea of formulas, and can modify existing formulas and create basic formulas. But even those around SAP Build Apps for a while still struggle to come up with complex formulas from scratch. This challenge will take you to the next level.

The challenges so far:

2023-12-24_18-40-33.png

The Real Purpose of Formulas

Essentially, formulas are a way to transform data – to convert data to a different data type, to aggregate, to reduce, to format.

So given that, we need to first understand the different “types” of data.

 

Type of Data

Before beginning, it helps to know the basics of the JSON format, a text-based data interchange format. There are plenty of sites for learning JSON, such as this one: https://developer.mozilla.org/en-US/docs/Learn/JavaScript/Objects/JSON  

Despite it looking complicated, essentially, all the data you deal with will be one of the following:

  • Primitive: String, number, Boolean, null

 

"My string"
100
True
null

 

  • Object: A single collection of fields, each of which is a primitive, list or another object

 

{a: “My String”, b: 100, c: True}
{a: 100, b: {c: “subobject”} }
{a: 100, b: [1, "Two", { x: 3 } ]}

 

  • Lists: A set of primitives, objects or (sub)-lists

 

[1, 2, 3, 4, 5, 6]

[
   {salesOrderID: 1234, NetAmount: 100.33},
   {salesOrderID: 9999, NetAmount: 3240.99}
]

[1, [2, 3], 4, "five", 6, {"next": 7} ]

 

These 3 “types” can be combined in all sorts of ways to get interesting data, but essentially that’s all there is.

 

Tools for Transforming Data

The formula editor is the tool you use to build formulas. Inside here, you have many functions to transform data from one type to another.

Dan_Wroblewski_0-1712724329287.png

For example, you search for functions that work on lists – these functions will take lists and transform them in some way. Take the MAP function. The documentation inside the formula editor is very good – it will provide a very good explanation of what the function does, with examples that you can actually modify and see how the function works.

The description tells you what type of data you start with and what type you end up with. Here, for MAP, it says it takes a list, and returns a list, but the contents of the list are transformed.

Dan_Wroblewski_1-1712724390577.png

Or take SUM, which takes a list of numbers and returns a number..

Dan_Wroblewski_2-1712724420419.png

Any function can be thought of this way: What type of data does it take, and what type of data does it return.

 

Strategy for Formulas (Example)

When you need to transform data, you should know what you are starting with and what you want to end up with, then figure out the intermediary steps for the transformation. Finally you have to find the functions that can do the type of transformations you need.

For example, let’s say you want to configure a dropdown list so the user can select a number that is in the range of 1 to some number, and that number is stored in a page variable.

 

pageVars.options

 

But the dropdown control needs a list of objects in this format:

 

[
{label: "1", value: "1"},
{label: "2", value: "2"},
{label: "3", value: "3"}
]

 

So, you need to come up with a strategy for transforming the primitive into a list of objects. Here’s one way to do this, step by step.

  • Since you need a list, you can use the GENERATE_RANGE function to transform the single number (primitive) to a list of numbers:

 

GENERATE_RANGE(1, pageVars.options)

 

This gets you this result.

 

[1,2,3,4,5]

 

  • But instead of a list of primitives, you need a list of objects. So you can use the MAP function to transform the items in the list -- because you know the MAP function transforms from one type of list to another type of list.

 

MAP(GENERATE_RANGE(1, pageVars.options),{label: item, value: item })

 

MAP iterates over your original list, and provides you the variable item that holds the current item in the list.

This gets you this result.

 

[
   {label: 1, value: 1},
   {label: 2, value: 2}
]

 

  • But the label and value fields must be provided as strings, so you will do another transformation, changing one type of primitive to another type of primitive. Here, you can use the STRING function to transform a number to a string.

 

MAP(GENERATE_RANGE(1, pageVars.options),{label: STRING(item), value: STRING(item)})

 

This gets you this result, which is what you need for your dropdown list.

 

[
   {label: "1", value: "1"},
   {label: "2", value: "2"},
   {label: "3", value: "3"},
   {label: "4", value: "4"},
   {label: "5", value: "5"}
]

 

 

 

Your Task

Part I

Using the app you worked on from Task 1, change the pagination so it returns 20 items per page.

Then add a button with the text Sum of Top 20.

20240407_110406623_iOS.png

When the user taps the button, set the stringToHash app variable using a formula to the sum of the 20 items in your data variable, and format the sum with a dollar sign, commas, and 2 decimal places, no spaces (there is a function that does this for you). Your number should look something like this (your amount will differ):

 

$123,456.99

 

Go to the Submit page, hash the string.

NOTE: If you need help with the formula, just take it one step at a time, and see the formula editor for all the formulas you can use. If needed, post in this thread for assistance.

 

Part II

You will use another formula, but this time to chart the data. Go to the Marketplace by clicking on Marketplace at the top of the UI components. Take some time to explore all the cool components (and flow functions) available.

Dan_Wroblewski_4-1712725045022.png

Find the pie chart component and install it.

Just below the button, add a container, make it white, and then add a pie chart to the container. At this point it should look like this, since you have no data:

20240407_111028036_iOS.png

Now you will set the data for the pie chart with a formula. Go to the Chart data property of the pie chart and create a formula to display the top 6 sales order items. Use:

  • Material for the display text
  • NetAmount for the data value

It should look something like this (your data will be different):

20240407_112459798_iOS.png

👉When you are done, take a screenshot, reply to this thread, and post the hash from Part I, and the screenshot Part II. Feel free to share your formulas.




--------------
See all my blogs and connect with me on Twitter / LinkedIn
63 REPLIES 63

ajmaradiaga
Developer Advocate
Developer Advocate
4,803

Hash: 8da8848f262caf6be3aaf0afeba809bd87b2821567bd156a841a32a51fc6f42d

ajmaradiaga_0-1712734718838.png

Very useful challenge to get familiar with Formulas!

 

brodierose
Explorer
4,777

1f9586d3056af3466272fe3bb5c7fa035fe6f37676b090ed6363e049dc47360f

Screenshot 2024-04-10 at 11.04.28 AM.png

brodierose
Explorer
4,734

Hi Dan,

I'm not sure if I misunderstood this, but in the post where you say "When the user taps the button, set the hashToSubmit app variable using a formula ... " would it not be the stringToHash app variable? Are we supposed to be hashing our value (eg. $123,456.99) or is that value we are supposed to use as our hash?

Thanks in advance!

4,717

@brodieroseyou are right.... it should be the stringToHash app variable, so that the value is set in the right field.

0 Kudos
4,664

Fixed.

Thanks for finding the error – I'd like to say it was meant to keep you on your toes but, alas, it was just my mistake 😸




--------------
See all my blogs and connect with me on Twitter / LinkedIn

encarrero
Participant
4,599

0ba8329d6eddbff79cc46fec982d4bdeecd38ba76210cdcde3ebf6536084fa10

encarrero_0-1712763574378.png

 

 

geek
Participant
4,590

9078d97aa244c244fc63fd87937e0c06192ced0ca03ea85eb8773484dc84037e

geek_0-1712773667622.png

@Dan_Wroblewski Now that was a, "Challenge".

4,551

One does have to question how well a citizen developer would have coped with a requirement like this.

mwn
Participant
4,475

The formula I have come up with for part 1 is:

FORMAT_LOCALIZED_DECIMAL(SUM(MAP(SPLIT(STRING(MAP(data.A_SalesOrderItem1,STRING(item.NetAmount))), ","),NUMBER(item))),"en",2)

 

*** once I checked out other responses, I found a much neater formula. It was all down to the NUMBER() function.

e82f04fe9965a1825fcd183922e42b4e6d1a75ddab1f8b871e3aadecfd64fed3

IMG_3295.png

 

 

I'm sure there's an easier way to sum the 20 NetAmount values, but everything else I came up with ended with 0.

 

ajmaradiaga
Developer Advocate
Developer Advocate
4,452

My formula is similar but I convert NetAmount to a NUMBER inside the MAP function...

 

"$" + FORMAT_LOCALIZED_DECIMAL(SUM(MAP(data.A_SalesOrderItem1,NUMBER(item.NetAmount))), "en", 2)

 

and for the chart I have the following:

 

MAP(SLICE(data.A_SalesOrderItem1, 0, 5), {x: item.Material, y: NUMBER(item.NetAmount)})

 

 

4,429

Took a middle ground between those, MAPping data.A_SalesOrderItem1 to { "NetAmount" : NUMBER( ) } and then SUM_BY_KEY( .... "NetAmount"). Didn't think to look at SUM...

4,420

I assume that, if the Sales Orders weren't ordered already, you'd have to put in an ORDER(data.A_SalesOrderItem1,item.NetAmount) like this : 

MAP(SLICE(ORDER(data.A_SalesOrderItem1,item.NetAmount), 0, 5), {x: item.Material, y: NUMBER(item.NetAmount)})

Did I understand correctly?

4,277

You are right, but in this case we've leveraged the power of OData 💪 to receive the response in the right order.

0 Kudos
3,024

I used a similar approach: I also set the maxDecimalCount to "2" to get always 2 decimal places.

"$" + FORMAT_LOCALIZED_DECIMAL(SUM(MAP(data.A_SalesOrderItem1,NUMBER(item.NetAmount) * 100)) / 100,"en",2,2)

For the chart data I have used a mapping with a formula for the Source Data (SLICE might be faster instead of SELECT)

SELECT(data.A_SalesOrderItem1, index < 6 )

Mapping for X -> source.item.Material
Mapping for Y -> NUMBER(source.item.NetAmount)

Dan_Wroblewski
Developer Advocate
Developer Advocate
0 Kudos
4,219

Unfortunately the hash does not look OK ... can you check the amount and the formatting and that you have the top 20?




--------------
See all my blogs and connect with me on Twitter / LinkedIn

3,095

My latest hash is

e82f04fe9965a1825fcd183922e42b4e6d1a75ddab1f8b871e3aadecfd64fed3

 

thanks

beny_illyes_mhp
Explorer
4,440

beny_illyes_mhp_0-1712823226064.png

8951c01475e11947e1522979a20e2ad4fca61c9ec1891b3868c6edcfcc93b908

I initially used a GENERATE_RANGE to map the NetAmount, wasn't sure how to dynamically handle them. The code from @ajmaradiaga though is the better approach for handling general cases

 

RameshShrestha
Contributor
4,233

Hi @Dan_Wroblewski 

Corrected Hash:

25dcc0e57a2c52bdee5cebfa7a727ce5a7a3b165677f96516100aad260bb9bef

I had one more formula added on click of Hash button. It was added during practice and missed to remove. Please check . Thanks for your suggestion.

RameshShrestha_0-1712839766615.png

Formula for stringtoHash:

STRING('$' +FORMAT_LOCALIZED_DECIMAL(SUM(MAP(GENERATE_RANGE(0, 19),NUMBER(data.A_SalesOrderItem1[item].NetAmount))), "en", 2))

Formula for Chart :

MAP(GENERATE_RANGE(0, 5) ,{x: data.A_SalesOrderItem1[item].Material, y: NUMBER(data.A_SalesOrderItem1[item].NetAmount)})

0 Kudos
3,327

Screenshot looks nice ... Unfortunately, the hash is not correct. I suggest use format "en" instead of "en-IN". Then reply with the new hash.




--------------
See all my blogs and connect with me on Twitter / LinkedIn

0 Kudos
2,396

Hi @Dan_Wroblewski ,

 I tried with the solution, still I am getting the same Hash. I tried adding all the numbers as well. And sum too gives same value. Could not figure it out exact reason for not matching the Hash.

Any other suggestions will be helpful. In between ,I will try to figure it out other points if I missed any. I have attached the screenshot of the Hash screen for reference.

RameshShrestha_0-1713331788350.png

 

2,378

I am assuming you are using the format function with "en-IN". Try using just "en".

The number to the left of the decimal should have commas after every 3 places.




--------------
See all my blogs and connect with me on Twitter / LinkedIn

0 Kudos
2,323

Got the issue with your help, it was overwritten in another formula.

YogSSohanee
Participant
4,208

7a5e014abc4707b886ca22339433cc8943030e430df70abb725bf9d549e967c4

I also tried to achieve this keeping Javascript in between and was able to do it, was real fun to do it via both Formula and JS. Keep 'em coming! 🙂

Day2.jpg

eshrinivasan
Developer Advocate
Developer Advocate
4,212

832e0fd4f33ec623e88dca8076ce6a07a3b9dccdb417dbe1d5ab6de9cf25ff7b

eshrinivasan_0-1712846620627.png

 

Jonathan_Fr
Explorer
4,097

b426d64032e8206f57f497558da20ae683848b32cf58af414480b9dd047d15be

Jonathan_Fr_0-1712871210120.png

 

0 Kudos
1,766

Hi, Unfortunately, the hash seems to be incorrect. The community ID shown in the screenshot is not the same as the correct community ID which is displayed on top of your reply.

Anumala
Discoverer
0 Kudos
3,951

1127937de75ccac1087a5b75f6eca404fa6fdb32c67feb538c019c08c27124c6

Anumala_0-1712909669723.jpeg

 

0 Kudos
3,354

The screenshot looks good, but the hash is not quite right ... again because the community ID should be the ID that appears at the top left of each of your replies. Please try again and post the new hash.




--------------
See all my blogs and connect with me on Twitter / LinkedIn

0 Kudos
2,517

@Dan_Wroblewski I hv created two community ID. Please check the new hash with community id- 'anumalasingh'
sum- $16,427,204.00

hash- 1127937de75ccac1087a5b75f6eca404fa6fdb32c67feb538c019c08c27124c6

anumalasingh_0-1713291901049.jpeg

 

Alpesa1990
Participant
3,576

My submission,

031b74229982b2e58aadb9d3fbbf81cc17877840a2e3a54faad9db55e6e868f8

 

Alpesa1990_0-1712942162038.png

 

stickman_0x00
Explorer
3,568

dbbea218a16958924fac9b9978b36a54cabc78b9c940f9b2687a2581d482733d

stickman_0x00_2-1712944124607.png

 

 

 

PieterB
Explorer
3,560

Let us start the weekend by finishing this challenge - Hopefully my hash is correct 😉

cd838b3c8b21b6b71941d1b9523e14298d896179921931762e72060a2c5c26b1

PieterB_0-1712944357905.png

Have a nice weekend and already looking forward to the next one!

VinayKumawat
Product and Topic Expert
Product and Topic Expert
3,209

Hi Dan,
Thanks for another challenge, I learned something with each of your challenges.
My Hash: bc2a37c36c34280d849d86967dbea24aa340d835ecf74b66da826f9245b74612
Screenshot 2024-04-15 at 1.14.32 PM.png

M-K
Participant
3,116

My Hash: ec7c82663c27ad05879c9b5d995e9922b806687234afd5e9e63e2271ea853d6f

build3.jpg

SantoshC
Explorer
3,065

Hi @Dan_Wroblewski 

Please find Hash Code

a091aee75be1679d7e00d51c35258b49509f78d731abb7eb051d19d8c2f55d43

SantoshC_0-1713195384992.png

 

0 Kudos
3,025

Unfortunately, I think the hash is not correct. Can you make sure you have formatted the string the right way (with "en" as formatting language) and have the top 20 items?




--------------
See all my blogs and connect with me on Twitter / LinkedIn

2,986

Hi @Dan_Wroblewski 

a6204122608ebc2b7aa5c69a1da24f27e96a7398fca3b459e917fd5fee2139fd

Could you please check this. 

SMaaLL
Participant
2,921

Hello Dan,

My Hash is bbc6e97be15ffff6538ab0d64853765ab0bdd83b8d77cff8a52ada3d5e6d7af1

SMaaLL_0-1713210660042.png

 

 

René
Product and Topic Expert
Product and Topic Expert
2,884

b52e22484da4a0a9e4efaae41e22f3384f6c1a3f81faa69e904be85949a8a109

Ren_0-1713212805226.png

 

To come up with a formula that works was really tough for me. I couldn't have done it without help from the comments here.

Thanks @RameshShrestha and @ajmaradiaga for sharing your formulas.