Formula Column JavaScript with Input from User

[APBowls]

Hello everyone,

I'm following up on a thread #9300 where I wrote a JS Function in a formula column to calculate point values.

The Formula in TOTALPOINTS Column:
(($("Points").map(q => q.Points).reduce((a, b) => a + b))

I now need to implement a feature that allows the end user to select a date range, and only perform the calculations on rows within that range. Is there a way to take input from the Production State back into the formula column in the Data Workspace? The goal would be to edit the formula to only iterate through the points values within the date range.

Any help would be greatly appreciated.

[melohagan]

Hey @APBowls

Formula columns only do calculations at the Data level. You cannot pass values through to them from the front-end Design section.

If you wanted to calculate points for a user specified range then you would need to do that calculation in a JavaScript binding within the Design section.

So instead of getting relationship data through a formula, you would get it through a front-end repeater:
https://docs.budibase.com/docs/fetching-data#displaying-relational-data

The filtering on the front-end would be limited by the Data provider pagination, however if the user is filtering the data then hopefully this won't be an issue if you set the limit and pagination sufficiently high enough.

[melohagan]

Did you try adding the Data provider rows binding from the bindings helper in the left-hand side panel within the filter drawer (rather than manually type it out.

Is Points definitely the case-sensitive correct name for the column in the Bi-Weekly Production table. Is the Dataprovider definitely using this table?

[APBowls]

Oh, I think I see the problem. I am doing additional math in the JavaScript Binding which references a different table. Any way to relate both tables to the one card component?

[melohagan]

Hmm, would you be able to provide me with screenshots of the different tables involved and the calculation you want to do between the tables please?

Then I can try and replicate it myself.

[APBowls]

I can do an app export if that is any help. It is all a bit messy, I'm still getting the hang of this.

[melohagan]

Yeah the app export would be great - you can send it to support@budibase.com

[melohagan]

Hey @APBowls

Thank you for the app export. I believe I've figured this one out, although I'm not certain that it's the use-case you're after.

It was a little bit tricky.

First I ejected the Card Block you had added, and nested another Data Provider for the Employees relationship. This was to allow me access to the Regular Hours and Research Hours.

Then the JavaScript binding for the card description looks as follows:

let regularHours = $("Employees Data Provider.Rows").find(emp => emp._id === $("Repeater.Bi-Weekly Production.Employees")[0]._id)["Regular Hours"]

//pph stands for 'Points Per Hour', Regular Hours reference from Pay Period Table
let pph = ($("Dataprovider.Rows").map(q => q.Points).reduce((a, b) => a + b)) / regularHours.toFixed(1)

//18.00 is pay for research and development hours, referenced from Pay Period Table
let rd = 18 * $("Employees Data Provider.Rows").find(emp => emp._id === $("Repeater.Bi-Weekly Production.Employees")[0]._id)["Research and Development Hours"]

if (pph < 8) {
  return (0.00 * regularHours) + rd

...etc

[melohagan]

To be honest this particular use-case would be easier with SQL, because then you could do the JOINs you need with a Custom query

[APBowls]

I have not been able to get this to work unfortunately. I will look into SQL, I'm not particularly knowledgeable about it, hence attempting to use internal tables. Thank you for taking a stab at it, I'm sure it's something small I'm not seeing.