Skip to main content
Solved

Group by function using scripting app & populate new table

  • October 29, 2020
  • 4 replies
  • 74 views
T

Hi Community,

I have a table ORDER of n orders with the {fields} : order-id, product, quantity, delivery-date

I am trying to use a script to group_by (product, delivery-date) in order to sum the field {quantity} of a group (total-quantity)

Then:

with Automation, create a record in a table GROUP-ORDER with the fields: order-group-id, delivery-date, product, total-quantity

Example on how the results should look like, is shared in the link above.

I’m a little knew to the Scripting App (and my skills in javascript are medium).

Is this approach feasible? and how should I get started?

I managed to group the fields with a script as in : I need help for solving a problem on the script block
However, I am struggling to create a new group-order record in a separate table.

Thanks!

Best answer by JonathanBowen

Hi @Tim_Khan - I wrote a post about a similar scenario where data is summarised and push to a new table:

4 replies

JonathanBowen
Forum|alt.badge.img+18
  • JonathanBowen
  • Inspiring
  • 1110 replies
  • Answer
  • October 29, 2020

Hi @Tim_Khan - I wrote a post about a similar scenario where data is summarised and push to a new table:

T
  • Tim_Khan
  • Author
  • New Participant
  • 3 replies
  • October 29, 2020

Hi @JonathanBowen

Wow, thanks for the article! a perfect script - worked on my side :slightly_smiling_face:

record.getCellValue(‘Name’) works fine with a text field, however when ‘Name’ is a lookup field, their is no-more distinction on the fields name. Do you have any trick for this issue?

JonathanBowen
Forum|alt.badge.img+18
  • JonathanBowen
  • Inspiring
  • 1110 replies
  • October 30, 2020

Hi @JonathanBowen

Wow, thanks for the article! a perfect script - worked on my side :slightly_smiling_face:

record.getCellValue(‘Name’) works fine with a text field, however when ‘Name’ is a lookup field, their is no-more distinction on the fields name. Do you have any trick for this issue?


Hi @Tim_Khan - if your field is a lookup you can do:

record.getCellValueAsString('Lookup field name')

Does that solve your problem?

T
  • Tim_Khan
  • Author
  • New Participant
  • 3 replies
  • October 30, 2020

Hi @Tim_Khan - if your field is a lookup you can do:

record.getCellValueAsString('Lookup field name')

Does that solve your problem?


thanks @JonathanBowen ! works perfect now

thanks again for your clean script that correlates well with SQL query SELECT SUM () FROM () GROUP BY ()


Terms & ConditionsAccessibility statement