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

[Dan_Wroblewski]

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:

• Task 0: Set Up SAP Build Apps
• Task 1: OData 
• Task 2: Formulas (this post)
• Task 3: Theming, Styling and Layout Design
• Task 4: Mobile device capabilities

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.

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.

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

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.

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.

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:

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

👉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.

[ajmaradiaga]

Hash: 8da8848f262caf6be3aaf0afeba809bd87b2821567bd156a841a32a51fc6f42d

Very useful challenge to get familiar with Formulas!

[brodierose]

1f9586d3056af3466272fe3bb5c7fa035fe6f37676b090ed6363e049dc47360f

[brodierose]

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!

[ajmaradiaga]

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

[Dan_Wroblewski]

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 😸

[encarrero]

0ba8329d6eddbff79cc46fec982d4bdeecd38ba76210cdcde3ebf6536084fa10

[geek]

9078d97aa244c244fc63fd87937e0c06192ced0ca03ea85eb8773484dc84037e

@Dan_Wroblewski Now that was a, "Challenge".

[geek]

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

[mwn]

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

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]

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

[geek]

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...

[beny_illyes_mhp]

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?

[ajmaradiaga]

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

[M-K]

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]

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

[mwn]

My latest hash is

e82f04fe9965a1825fcd183922e42b4e6d1a75ddab1f8b871e3aadecfd64fed3

thanks

[beny_illyes_mhp]

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]

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.

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

[Dan_Wroblewski]

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

[RameshShrestha]

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.

[Dan_Wroblewski]

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.

[RameshShrestha]

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

[YogSSohanee]

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! 🙂

[eshrinivasan]

832e0fd4f33ec623e88dca8076ce6a07a3b9dccdb417dbe1d5ab6de9cf25ff7b

[Jonathan_Fr]

b426d64032e8206f57f497558da20ae683848b32cf58af414480b9dd047d15be

[eshrinivasan]

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]

1127937de75ccac1087a5b75f6eca404fa6fdb32c67feb538c019c08c27124c6

[Dan_Wroblewski]

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.

[anumalasingh]

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

hash- 1127937de75ccac1087a5b75f6eca404fa6fdb32c67feb538c019c08c27124c6

[Alpesa1990]

My submission,

031b74229982b2e58aadb9d3fbbf81cc17877840a2e3a54faad9db55e6e868f8

[stickman_0x00]

dbbea218a16958924fac9b9978b36a54cabc78b9c940f9b2687a2581d482733d

[PieterB]

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

cd838b3c8b21b6b71941d1b9523e14298d896179921931762e72060a2c5c26b1

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

[VinayKumawat]

Hi Dan,
Thanks for another challenge, I learned something with each of your challenges.
My Hash: bc2a37c36c34280d849d86967dbea24aa340d835ecf74b66da826f9245b74612

[M-K]

My Hash: ec7c82663c27ad05879c9b5d995e9922b806687234afd5e9e63e2271ea853d6f

[SantoshC]

Hi @Dan_Wroblewski 

Please find Hash Code

a091aee75be1679d7e00d51c35258b49509f78d731abb7eb051d19d8c2f55d43

[Dan_Wroblewski]

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?

[SantoshC]

Hi @Dan_Wroblewski 

a6204122608ebc2b7aa5c69a1da24f27e96a7398fca3b459e917fd5fee2139fd

Could you please check this. 

[SMaaLL]

Hello Dan,

My Hash is bbc6e97be15ffff6538ab0d64853765ab0bdd83b8d77cff8a52ada3d5e6d7af1

[René]

b52e22484da4a0a9e4efaae41e22f3384f6c1a3f81faa69e904be85949a8a109

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.