[CAP] BAS – port 4004 is already in use by another server process

If the default port 4004 is already open and you want to see what is bound to it, select View -> Find Command -> Ports Preview

Solution to kill another “watch.js” process using/blocking the port: https://answers.sap.com/questions/13016130/sap-business-application-studio-stop-running-serve.html
To archive the same in a single command use:

lwctl -c basic-tools kill -9 $(lwctl -c basic-tools ps aux | grep watch.js | awk '{print $2}')

As alternative, change the default port by adding a new port in package.json to the start script, for example: “start”: “cds run –port 4003” and use npm run start instead of cds watch.

[CAP] Query teamMembersSize from SF

const sfsfSrv = await cds.connect.to('sfsf')

// Option 1: Query Notation
const response = await sfsfSrv.run(SELECT`teamMembersSize`.from`User`.where`userId = ${req.user.id}`)
console.log("option 1: " + response[0].teamMembersSize)

// Option 2: HTTP method-style
const response2 =  await sfsfSrv.get(`/User('${req.user.id}')/teamMembersSize/$value`)
console.log("option 2: " + response2)

[CAP] Easy way to test if your approuter is working

An easy way to test your approuter is using the user-api-service. For that add the following route to your xs-app.json

    {
        "source": "^/user-api(.*)",
        "target": "$1",
        "service": "sap-approuter-userapi"
    }

And after deployment, open the application router URL and add /user-api/currentUser to it. You should see your Email and other User details. This is testing that the application router is actually getting the security token from the UAA instance.

[CAP] Set credentials in package.json via .env

https://developers.sap.com/tutorials/btp-app-ext-service-add-consumption.html#ebedc445-68de-4cd2-befd-6e31897852a8

These three entries in a .env file

cds.requires.ECEmploymentInformation.[development].credentials.authentication=BasicAuthentication
cds.requires.ECEmploymentInformation.[development].credentials.username=myUsername
cds.requires.ECEmploymentInformation.[development].credentials.password=myPassword

are equal to line 12, 13, and 14 in this package.json snippet:

{
    "cds": {
      "ECEmploymentInformation": {
        "kind": "odata-v2",
        "model": "srv/external/ECEmploymentInformation",
        "credentials": {
          "[production]": {
            "destination": "sfsf"
          },
          "[development]": {
            "url": "https://apisalesdemo2.successfactors.eu/odata/v2",
            "authentication": "BasicAuthentication",
            "username": "myUsername",
            "password": "myPassword",
          }
        }
      }
    }
}

[CAP] SAPUI5 Filter using FilterOperator.Contains with oData V2

In my SAPUI5 Freesstyle frontend I created a search field above a list. In the searchfield handler I’m creating a filter with the provided query.

const sQuery = oEvent.getParameter("query");
new Filter("firstName", FilterOperator.Contains, sQuery);

Afterwards I’m binding the filter to my list to trigger the binding refresh. But when debugging the backend handler I noticed the following…

In my CAP on Read handler, the filter gets converted into a V4 compatible filter expression:

oData V4: $filter=contains(firstName,'Max')

As I’m forwarding the request to an external V2 oData API (SuccessFactors) this would not work, as for V2 the following filter syntax is needed:

oData V2: $filter=substringof('Max',firstName) eq true

As I could not find any solution to this problem, I manually passed my filter as custom property to my CAP Service and did a manual select.

Adding the custom property in the frontend in my searchfield handler:

onSearch: function (oEvent) {
			if (oEvent.getParameters().refreshButtonPressed) {
				this.onRefresh();
				return;
			}

			let oBindingInfo = this._oList.getBindingInfo("items");
			if (!oBindingInfo.parameters) oBindingInfo.parameters = {};
			if (!oBindingInfo.parameters.custom) oBindingInfo.parameters.custom = {};

			if (oEvent.getParameter("query")) {
				oBindingInfo.parameters.custom.filter = "%" + oEvent.getParameter("query") + "%";
			} else {
				oBindingInfo.parameters.custom.filter = undefined
			}
			this._oList.bindItems(oBindingInfo);
}

My CAP handler with the filter handling:

const { Object } = srv.entities
const SF_Srv = await cds.connect.to('SF')

srv.on('READ', Object, async req => {

            if (!req._queryOptions.filter) {
                // share request context with the external service 
                return SF_Srv.tx(req).run(req.query);
            } else {
                //if filter provided, build manually a select statement using a where condition
                let input = req._queryOptions.filter;
                const tx = SF_Srv.transaction(req);
                return await tx.run(
                    SELECT
                        .from(Object)
                        .where`firstName like ${input} or lastName like ${input}`)
            }
    })

As alternative you could also add the where condition directly to the query object:

const { Object } = srv.entities
const SF_Srv = await cds.connect.to('SF')

srv.on('READ', Object, async req => {

            if (req._query.filter) {
                //if filter provided, build manually a select statement using a where condition
                let { query } = req
                let input = req._queryOptions.filter

                if (!query.SELECT.where) query.SELECT["where"] = []

                query.SELECT.where.push(
                    { ref: ['firstName'] }, 'like', { val: input }, "or",
                    { ref: ['lastName'] }, 'like', { val: input }, "or",
                    { ref: ['object'] }, 'like', { val: input })
            }

            // share request context with the external service 
            return SF_Srv.tx(req).run(req.query)
    })

[CAP] Using a Tree in SAPUI5 Freestyle app

The following Links helped me implementing the tree functionality:

https://sapui5.hana.ondemand.com/#/entity/sap.m.Tree/sample/sap.m.sample.TreeOData

https://answers.sap.com/questions/13192367/sap-cds-how-to-add-hierarchy-annotations-saphierar.html

Define the data model in data-model.cds

entity Node {
    key NodeID         : Integer;
        HierarchyLevel : Integer;
        ParentNodeID   : Integer;
        Description    : String;
        drillState     : String;
}

Create testdata in my.test-Node.csv

NodeID;HierarchyLevel;ParentNodeID;drillState;Description  
1;0;null;"expanded";"1"
2;0;null;"expanded";"2"
3;0;null;"expanded";"3"
4;1;1;"leaf";"1.1"
5;1;1;"expanded";"1.2"
6;2;5;"leaf";"1.2.1"
7;2;5;"leaf";"1.2.2"

and deploy the testdata to your local sql db

cds deploy --to sqlite:db/test.db

Service Definition in test-service.cds

using my.test as db from '../db/data-model';

service testService {
     entity Nodes as projection on db.Node;
}

add the Tree controll to your Fiori UI view Tree.view.xml

		<Tree
		    id="Tree"
		    items="{path: '/Nodes',
				    parameters : {
		                countMode: 'Inline',
                        numberOfExpandedLevels: 3, 
                        treeAnnotationProperties: { hierarchyLevelFor : 'HierarchyLevel', 
                                                    hierarchyNodeFor : 'NodeID', 
                                                    hierarchyParentNodeFor : 'ParentNodeID', 
                                                    hierarchyDrillStateFor : 'drillState' 
                                                    }
		            }
            }">
			<StandardTreeItem title="{Description}"/>
		</Tree>

The output should be similar to this:

[CAP] Add SQLite DB for development

# install SQLite
npm i sqlite3 -D 

# create db, save configuration in package.json, stores mock data into db
cds deploy --to sqlite:db/my-app.db

# test cds deploy command with --dry. Displays ever table and view it creates
cds deploy --to sqlite:db/my-app.db --dry

# get and overview of your tables with .tables
sqlite3 db/my-app.db .tables

# open and view newly created db
sqlite3 db/my-app.db -cmd .dump

# and select single field with
SELECT field FROM mytable WHERE mykeyfield= "00505601194D1EE9B7BFC518B85";

# update a field with
UPDATE mytable SET field = "test" WHERE mykeyfield= "00505601194D1EE9B7BFC518B85";