How to display a value only once per order with serveral saleslines in a Novulo Export?

[Q&A] General
1

We are creating an export of sales lines in Novulo. If a sale consists of multiple saleslines, we want the value to appear only on the first line of that sale, while the other saleslines remain empty.

Currently, we are using the following expression:

float:first({
expectedtransactions,
this.amount().round(),
boolean:and([
this.payment_method.id.equals(10),
this.first_invoice.only_sales.equals(parent.sales)
])
})

My question is: is there a way to ensure that the value only appears once per sale in Novulo, without repeating it on every line?

KR,
Stephan.

2

If you want to export a value only for 1 sales line in a sales, you can use the following approach:

{
 saleslines,
 <
  id,
  string:if(
   saleslines:first({
    saleslines,
    this,
    boolean:and([id.islower(parent.id),sales.equals(parent.sales)])
   }).isnull(),
   "I am the first",
   "I am not the first"
  )
 >,
 this.sales.id.equals(1)
}

Run the expression in your debugger to see the result. Switch the filter on sales 1 to an ID that suits you.

In my case, i got this result:

40903	"I am the first"
40904	"I am not the first"
40931	"I am not the first"
40932	"I am not the first"
40933	"I am not the first"
40934	"I am not the first"
40935	"I am not the first"
  1. Use an IF statement to either display value A or B. Or, in your case, either the amount of the expected transaction or ā€œ0ā€.
  2. Define a condition in the IF that is only valid for 1 sales line per sales. In each sales, there can be only 1 sales line with the lowest ID. Or in other words, there does not exist a sales lines with a lower ID in the same sales for the sales line which has the lowest ID in the sales.

This approach is not very fast, but it should work. I hope this helps.

1 Like
3

What is the purpose of your export? If you are creating an exportdefinition for an activity, you can use a nested grid. Also you can display saleslines and let word do the thinking, if you donā€™t use contentblocks.

If you make an excelsheet, I would wonder why you wouldnā€™t want the value, because creating tables is a lot easier that way. if you want to make an xml export, just nest it under the sales and donā€™t export the salesnumber under the saleslines, use ../number instead to get it.

4

Hi Helena and Joachim,

Thank you both for your responses,

The purpose of the export is to generate a financial dataset that combines multiple elements into an XLSX file using Novuloā€™s report export functionality. (That works great :slight_smile: ). This file is then imported into another financial software system. To ensure data integrity, the export must avoid duplicate rows. In this case the expectedtransaction amount value must not appear repeatedly for each salesline in an order. If it does, it could create the false impression that multiple expected transactions are associated with the same order. This approach ensures clarity and accuracy in the exported data structure.

Iā€™ve created the following expression:

{
 saleslines,
 <
  this.id,
  float:first({
   expectedtransactions,
   this.amount().round(),
   boolean:and([
    this.payment_method.id.equals(10),
    this.first_invoice.only_sales.equals(parent.sales)
   ])
  }).isnull(),"",
  float:first({
   expectedtransactions,
   this.amount().round()})
 >}

This will show results in the debugger, but in the exportfield it will show

An error occurred, Type check error in ā€˜%2.tostring()ā€™: Can only apply data functions to single data types, 2%.tostring()

So what am I doing wrong? :blush:

5

mmh the expression does not seem to use the approach that I suggested regarding the ā€œfirst salesline of a salesā€. This part is missing as a condition in the IF:

saleslines:first({
    saleslines,
    this,
    boolean:and([id.islower(parent.id),sales.equals(parent.sales)])
   }).isnull()

Furthermore, is XLSX the only option for you?
I have the feeling that for what you are trying to achieve, a nested file like an XML or a JSON would be better suited. Something like this:

<sales>
  <amount>123.45</amount>
  <saleslines>
    ...
  </saleslines>
</sales>

{
  "sales": {
    "amount": 123.45,
    "saleslines": [ ... ]
  }
}

If you want, we can schedule a meeting to look at the challenge you are facing together in detail. Please create an SR for that.