topic Re: Task 9 - Using CQL in an unbound action (July Developer Challenge - "Reverse APIs") in Application Development and Automation Discussions

Task 9 - Using CQL in an unbound action (July Developer Challenge - "Reverse APIs")

qmacro — Fri, 26 Jul 2024 12:31:55 GMT

This is a task in the July Developer Challenge - "Reverse APIs".

In this task you'll define and implement an unbound action, that does something that you might remember from the Developer Challenge on APIs last year in August, and you'll end up writing some (hopefully) interesting code for the implementation.

Background

Last year we ran a Developer Challenge on APIs. In one of the tasks - Task 3 - Have a Northbreeze product selected for you - you needed to make a call to an API endpoint supplying your SAP Community ID and it would return a Northwind product. In the example for my ID, "qmacro", it showed this being returned:

{
  "@odata.context": "$metadata#Edm.String",
  "value": "Rössle Sauerkraut"
}

In other words, the product "corresponding to" my SAP Community ID is "Rössle Sauerkraut". Different products were returned for different SAP Community IDs, so that your answers would be different to each other. The calculation of which product to return was based on the value of your SAP Community ID.

In this task, you will need to define an API endpoint, in the form of an unbound action, and write the implementation to calculate, look up and return the name of a product that corresponds to the SAP Community ID that is sent by the TESTER.

Correspondence calculation

So how should you determine which product to select for a given SAP Community ID? The same way that it was determined in last year's Developer Challenge for that task. And that is to turn the SAP Community ID string value into a numeric value, and then use that to select a specific product with an ID (in ProductID) that matches that numeric value.

Here's the logic:

First:

ensure the SAP Community ID is all lower case
convert each character to its equivalent decimal ASCII code
add the resulting list of ASCII codes together to make a total

Then:

determine how many products are in your Northbreeze data
read a single product where the key is the ASCII codes total value

Noting that:

you will need to use modulo arithmetic to ensure that the ASCII codes total value falls within the range between 1 and the total number of Northbreeze products

And here's an example of that logic in action, where (for the sake of illustration) the SAP Community ID value "QmacrO" is sent in the payload of the HTTP request:

converting "QmacrO" to lower case gives "qmacro"
the decimal ASCII codes for "qmacro" are 113, 109, 97, 99, 114 and 111
added together, these codes come to 643
there are 77 Northbreeze products
using modulo arithmetic to turn 643 into a number between 1 and 77, it becomes 28
the product with ID 28 is Rössle Sauerkraut

Be careful with the modulo calculation, because you need to take into account that the result of a modulo calculation could be 0, which is not in the ID range. It would also never be 77. This is why 643 modulo 77 becomes 28, not 27 (basically, do the modulo calculation and add 1).

Clearly these instructions have already told you that there are 77 products. You should have 77 products too if you started with the repo as recommended. Nevertheless, we encourage you to use some CQL in your implementation to programatically determine how many products there are, not just set the value 77 in a constant 🙂

The requirements

Here are the specific requirements for this task.

Add an unbound action called selectProduct to the definition of your northbreeze service. It should expect a String value for a single parameter named communityid, and it should return the same type as the ProductName element in the Products entity type.

Note that although you will of course be supplying your SAP Community ID as normal when you submit to the TESTER (see the next section), as usual, it will be the TESTER that picks and sends an SAP Community ID to your endpoint, not you, of course.

In the implementation, use CQL to determine the number of products you have, and then use the logic described in the "Correspondence calculation" section earlier to determine the product ID, based on the (semi-random) SAP Community ID value received. Then use CQL to retrieve that product and return the product name.

Submitting to the TESTER

Now you're ready to submit your CANDIDATE service, with this new API endpoint, to the TESTER!

The payload

The task identifier you need to supply in the payload of your submission is: northbreeze-selectProduct.

You'll have already done this sort of thing previously so just head back there for the more detailed instructions if you need them, or to the the section titled "The Tester service, and making a test request" in the main challenge blog post.

You'll need to submit a JSON payload like this:

{
  "communityid": "<your-community-id>",
  "serviceurl": "<the-URL-of-your-service>",
  "task": "northbreeze-selectProduct"
}

And, just as with the previous (and all further tasks):

the value for the communityid property should be your ID on this SAP Community platform (e.g. mine is "qmacro")
the value for the serviceurl property should be the absolute URL (i.e. including the scheme), of your CANDIDATE service which contains the API endpoint (see ℹ️ A note on URLs and services).

That's it!

Logging of test results

Remember that you can check on your progress, and the progress of your fellow participants - all requests are logged and are available in an entity set served by the TESTER service. The entity set URL is https://developer-challenge-2024-07.cfapps.eu10.hana.ondemand.com/tester/Testlog and being an OData V4 entity set, all the normal OData system query options are available to you for digging into that information.

Until the next task, have fun, and if you have any questions or comments, leave them below!

Re: Task 9 - Using CQL in an unbound action (July Developer Challenge - "Reverse APIs")

Liyon_SV — Fri, 26 Jul 2024 09:19:48 GMT

Passed 😀

Re: Task 9 - Using CQL in an unbound action (July Developer Challenge - "Reverse APIs")

Liyon_SV — Fri, 26 Jul 2024 09:30:47 GMT

There seems to be a slight inconsistency in the instructions. The task initially mentions creating an unbound action but then later specifies requirements for an unbound function. Or perhaps I am misunderstanding the instructions.

Re: Task 9 - Using CQL in an unbound action (July Developer Challenge - "Reverse APIs")

mxmw — Fri, 26 Jul 2024 09:57:15 GMT

Passed!

Noticed the same as Liyon_SV and I went for the action as this is mentioned in title.

Re: Task 9 - Using CQL in an unbound action (July Developer Challenge - "Reverse APIs")

AndrewBarnard — Fri, 26 Jul 2024 10:56:03 GMT

The spread operator, map and reduce come in handy here.

Re: Task 9 - Using CQL in an unbound action (July Developer Challenge - "Reverse APIs")

cguttikonda24 — Fri, 26 Jul 2024 11:33:48 GMT

Hi DJ, @qmacro ,

Looks like the instructions inter-twined between action and function.

Did build a function and couldnt get pass the tester changed it to action and it works...

{ ID: "e373bede-7a34-467c-a988-99a0017b708d", task: "northbreeze-selectproduct", result: "PASS", createdAt: "2024-07-26T11:28:27.792Z", createdBy: "anonymous", modifiedAt: "2024-07-26T11:28:27.792Z", modifiedBy: "anonymous", serviceurl: "https://northbreeze-main.cfapps.us10-001.hana.ondemand.com/odata/v4/northbreeze", communityid: "cguttikonda24" },

Just wondering is there a fluent api approach on select to get count of records from a table.

Re: Task 9 - Using CQL in an unbound action (July Developer Challenge - "Reverse APIs")

qmacro — Fri, 26 Jul 2024 12:31:39 GMT

Awesome, well spotted. Mea culpa. Corrected (function -> action). Thanks!

Re: Task 9 - Using CQL in an unbound action (July Developer Challenge - "Reverse APIs")

qmacro — Fri, 26 Jul 2024 12:32:33 GMT

Thanks, yes, sorry for the confusion. Definitely should be an action. Corrected.

Re: Task 9 - Using CQL in an unbound action (July Developer Challenge - "Reverse APIs")

qmacro — Fri, 26 Jul 2024 12:36:17 GMT

Lovely! You say all the right things to pique my interest! Would love to see some example from you here!

In wanting to generate some sample tests to use in the TESTER module for this, I generated stuff with jq, "because jq". And you wouldn't believe how lovely this was - it just flowed from my keyboard, and was instantly beautiful. Not because of my coding prowess, but because of how jq has been designed. See the `getid` function here:

Re: Task 9 - Using CQL in an unbound action (July Developer Challenge - "Reverse APIs")

qmacro — Fri, 26 Jul 2024 12:38:41 GMT

Nice work, and sorry about the confusion.

You ask a good question - I'm not sure how this might work (altho I have a small brain) because we have to know the number of products in order to calculate the index, via the modulo of that number before diving in with that calculated index. That said, I'd love to hear from others who have other ideas!

Re: Task 9 - Using CQL in an unbound action (July Developer Challenge - "Reverse APIs")

Alpesa1990 — Fri, 26 Jul 2024 12:59:45 GMT

My submission fot task 9.

Congrats again! I faced to the same problem but solved it by switching to unbound action.

Regards

Re: Task 9 - Using CQL in an unbound action (July Developer Challenge - "Reverse APIs")

YogSSohanee — Fri, 26 Jul 2024 13:18:51 GMT

Initially created an unbound function and was receiving the value correctly.

But then realized after getting an error via the deployed application that instead of function, an unbound action is expected.

So changed it to action, but then started getting this error.

Can anyone please help if they have encountered this or have knowledge on this?

Re: Task 9 - Using CQL in an unbound action (July Developer Challenge - "Reverse APIs")

qmacro — Fri, 26 Jul 2024 13:27:19 GMT

Rather than me tell you directly, I'll give you a hint.

Functions are called with GET and parameters are sent inside parentheses at the end of the URL.

Actions, on the other hand, are called with POST and parameters are sent in the payload of the request, usually in JSON.

It most likely didn't help that I confused you into creating a function first, sorry. So - just check how you're making the call to your action. It is likely / possible that you've implemented it correctly - it's just that you're calling it like a function 🙂

HTH!

Re: Task 9 - Using CQL in an unbound action (July Developer Challenge - "Reverse APIs")

YogSSohanee — Fri, 26 Jul 2024 14:25:51 GMT

Thanks @qmacro for your valuable suggestion. I got it working now and cleared the difference between calling Functions and Actions as well.

PFB the tester response.

Re: Task 9 - Using CQL in an unbound action (July Developer Challenge - "Reverse APIs")

geek61 — Fri, 26 Jul 2024 16:55:40 GMT

A couple of curve balls in there to make things interesting. Thanks for this:

Re: Task 9 - Using CQL in an unbound action (July Developer Challenge - "Reverse APIs")

qmacro — Sat, 27 Jul 2024 06:14:09 GMT

🙂

Re: Task 9 - Using CQL in an unbound action (July Developer Challenge - "Reverse APIs")

AndrewBarnard — Sat, 27 Jul 2024 06:51:21 GMT

@qmacro

OK - now that your interest was piqued - here is what I did.

I took the communityid and using the spread operator, spread it from a String into an array of individual characters.

Noting that the map() method of an Array creates a new array populated with the results of calling a provided function on every element in the calling array, I used map with a function to convert to lower case and then determine the numeric value of each of the elements. ( the "characters").

The reduce() method of an Array executes a user-supplied "reducer" callback function on each element of the array, passing in the return value from the calculation on the preceding element "forwards". The final result of running the "reducer function" across all elements of the array is a single value. I used reduce to sum the numeric values into one single value.

So here it is:

const theSum = [...communityid].map(b => b.toLowerCase().charCodeAt()).reduce( (a,b) => a + b )

Re: Task 9 - Using CQL in an unbound action (July Developer Challenge - "Reverse APIs")

gphadnis2000 — Sat, 27 Jul 2024 08:59:01 GMT

my submission for task 9.Finally learned about unbound action and how to pass payload.Nice task.

Re: Task 9 - Using CQL in an unbound action (July Developer Challenge - "Reverse APIs")

sudarshan_b — Sat, 27 Jul 2024 21:13:15 GMT

Catching up this fantastic task during the weekend, here's my submission -

My CQL statement to determine the number of products (count) was working fine when tested locally (localhost:4004), but was failing after deploying to CF. It took me quite some time to figure out that the issue was the "count" function needs an alias to work in CF i.e.; 'count(ProductID) as count'. Strangely the "count" without alias works fine locally.

Finally, got it working with

New learning!! 🙂

Re: Task 9 - Using CQL in an unbound action (July Developer Challenge - "Reverse APIs")

M-K — Sun, 28 Jul 2024 17:58:54 GMT

Here's my submission.

Big thanks to @sudarshan_b, I ran into the very same problem and fixed it by adding an alias. The BTP application log was helpful here, but the error "❗️Uncaught Cannot read properties of undefined (reading 'ProductName')" was not really pointing me to search for the error at the SELECT count(*).