Announcement

Collapse
No announcement yet.

two records via the report (subtract)

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • two records via the report (subtract)

    Hi, I want to calculate two records via the report (subtract) how to do it please?

  • #2
    Hi,

    You need to create a notStorable int or float field manually (in metadata entityDefs) and specify an expression.

    custom/Espo/Custom/Resources/metadata/entityDefs/YourEntityType.json

    Code:
    {
        "fields": {
            "diffField": {
                "type": "float",
                "notStorable": true,
                "readOnly": true,
                "select": {
                    "select": "SUB:(field1, field2)"
                }
            }
        }
    }
    Where field1 and field2 are fields you want to substract, diffField is a field name of your new field. Name it as you wish.

    Clear cache.

    Then use this field in a report.

    You will be able to set a label for your new field at Administration > Label Manager.
    Last edited by yuri; 09-29-2021, 05:46 PM.

    Comment


    • #3

      Hi, thank you for your response. I tried it according to the instructions, but when I insert the field into the report, it only shows zero. Is something wrong?

      Comment


      • #4
        Originally posted by yuri View Post
        Hi,

        You need to create a notStorable int or float field manually (in metadata entityDefs) and specify an expression.

        custom/Espo/Custom/Resources/metadata/entityDefs/YourEntityType.json

        Code:
        {
        "fields": {
        "diffField": {
        "type": "float",
        "notStorable": true,
        "readOnly": true,
        "select": {
        "select": "SUB:(field1, field2)"
        }
        }
        }
        }
        Where field1 and field2 are fields you want to substract, diffField is a field name of your new field. Name it as you wish.

        Clear cache.

        Then use this field in a report.

        You will be able to set a label for your new field at Administration > Label Manager.

        Comment


        • #5
          I wonder why you have the same field in Group By and Columns. This seems wrong.

          To test whether the expression works, add it to the list view layout. You should see the value on the list view.

          And the "currency" type can't be used here.

          Comment


          • #6
            Originally posted by yuri View Post
            I wonder why you have the same field in Group By and Columns. This seems wrong.

            To test whether the expression works, add it to the list view layout. You should see the value on the list view.

            And the "currency" type can't be used here.

            Is there a way to use the currency? Because I count two fields that are currency.

            Comment


            • #7

              I tried to create two float fields, and then subtract them. It doesn't work for me either. Can you please look at it for me? I'll be happy to pay for it. Thank you

              Comment


              • #8
                It already works for me. But I have a problem .... if both fields are not filled in the record, it does not count the amount that is there itself.
                Last edited by sumservis; 10-02-2021, 10:36 AM.

                Comment


                • #9

                  You had to create a default 0 for both fields. Then everything worked as it should.

                  Big thank you

                  Comment


                  • espcrm
                    espcrm commented
                    Editing a comment
                    Nice of you to documents. Very helpful.

                • #10

                  It also works with currency.

                  Comment

                  Working...
                  X