Using Date Columns in the Formula Editor

[oscarlondoņo]

Dear Isomorphics!

When I need to have a ListGrid column with a numerical calculation, I use the Add Formula Column... option on the ListGrid context menu; then, I use arithmetic operations and the mathematical functions that SmartGWT has predefined (min, max, round, ceil, etc.).

I recently had the need to include calculations between dates within those formula fields. Unfortunately, I did not find posts related to this functionality; I only found that the dateAdd() function can be used, but I cannot include Date type fields in it, since they are not available in the column selector.

However, in order to illustrate what I need, I managed to recreate it by modifying this example, where I create a couple of Date fields and create a calculated field with the formula that returns the difference between those two Date fields. Below is the script I used for replacing the .js file script — which, by the way, only worked on the SmartGWT 11 demo —

isc.DynamicForm.create({
  ID:"order",
  width:500,
  fields: [

      { type:"header", defaultValue:"Subtract two dates" },
      { name:"shipDays", title:"Day to subtract", type:"integer", defaultValue: 15, visible:false },

      { name:"iniDate", title:"First Date", type:"date", canEdit:true,
        formula: { text: "DateAdd( new Date(), 'd', -shipDays)" }
      },
      { name:"endDate", title:"Last Date", type:"date", canEdit:true,
        formula: { text: "new Date()" }
      },
      { name:"dife2Dates", title:"Difference", type:"integer", canEdit:false,
        formula: { text: "round((endDate - iniDate) / (1000 * 3600 * 24), 0)" }
      }
  ]

});

I would like to know if there is any approach so that Date fields can be included in the field selector of the formula editor, and thus obtain, for example, the number of days between two dates.


Thanks in advance for your support!

Best regards,
Oscar Londoņo

[Isomorphic]

When you report an issue you need to provide the exact version of SmartGWT that you're using, including the release and patch build number. The easiest way to find this is to evaluate isc.version in the JavaScript console of your browser with the app open. You can also call the methods of class Version from inside SmartGWT if you like.

[oscarlondoņo]

Thanks for the instruction.

As I mentioned in the post, I used a SmartGWT 11 demo; but I'm working with SmartGWT Eval 13.0-p20220805 (actaully, I'm evaluating this version to see if it has what I need to purchase the Pro license), JDK 1.8.0_241, GWT 2.8.2.

[Isomorphic]

We see the issue you're talking about with the formula editor for grids not showing the date fields and we're looking into it. Of course, that's not an issue if you're coding the userFormula text yourself, as you can either bind the field in userFormula.formulaVars, or directly refer to the field in the text via record.<fieldName>. This is documented here.

What did you mean by "which, by the way, only worked on the SmartGWT 11 demo." Are you seeing a difference in behavior between releases, or between different SGWT sample projects?

[oscarlondoņo]

The workaround that you suggest (and that I appreciate a lot), could be an alternative, but the thing is that it is the user who decides which fields (including dates) make the most sense in certain formulas, that is, they can vary depending on each particular need.

What I meant about "which, by the way, only worked on the SmartGWT 11 demo." is that I used the same script (the one I posted) in the 12 and 13 demos and the non-visible field I used to subtract days from the first date didn't work: the days in that field they were not subtracted from the first date. Then I realized that subtracting the dates worked fine if I manually changed the dates.

I'll be waiting if you release any solution to include Date fields in formulas, which would be great ;).

[oscarlondoņo]

Dear Isomorphic:, one more question:

In which library could I find the "Formula Builder" class and in what way could I extend its operation, so that I can add those date type fields inside the formulas?

[Isomorphic]

What I meant about "which, by the way, only worked on the SmartGWT 11 demo." is that I used the same script (the one I posted) in the 12 and 13 demos and the non-visible field I used to subtract days from the first date didn't work: the days in that field they were not subtracted from the first date. Then I realized that subtracting the dates worked fine if I manually changed the dates.

I'll be waiting if you release any solution to include Date fields in formulas, which would be great ;).

The fixes for dateAdd() not working in recent releases and for showing date fields in the FormulaBuilder have already been applied to our debug branch and will be ported back to older releases shortly. Check this thread for updates.

In which library could I find the "Formula Builder" class and in what way could I extend its operation, so that I can add those date type fields inside the formulas?

The FormulaBuilder is in the Grids Module (ISC_Grids.js), if that's what you are asking, but we'd recommend that you wait for the official fixes for the issues you've reported rather than making your own modifications. In many cases, trying to fix reported framework bugs yourself might involve modifying undocumented properties and methods, which are not guaranteed to exist in future releases.

[oscarlondoņo]

It's ok, I really appreciate your feedback.

Do you have a projected release date for that change?

[Isomorphic]

These issues have been addressed:

• The FormulaBuilder GUI has been enhanced back to the SC 12.1 release to include date fields
• The dateAdd() formula math function should now work in all releases where it was broken (so SC 12.0+)
• In the SC 13.1 release, there was a separate issue with all date-typed formulas not working that's been fixed

These fixes should be in all of the nightly builds dated 2023-01-20 and beyond.

[oscarlondoņo]

Hi, Isomorphic!

I tested the last SC 13.0 release and I could include the date fields in a formula, and calculations worked as expected.

Thanks, again!