Count Currency in Grid Report

[bradaks]

Version: 5.0.5
AP Version: 1.30.7

I am having issues making a grid report. When viewing available columns I can only see the converted amounts. Unfortunately, those fields are blank while the currency field itself has data. Is there a way to pull my "Total Paid" count instead of my "totalPaidConverted"?

[tanya]

Hello
could you check currency table in the database? Also check if is set total_paid_currency column in your entity table

[yuri]

Hi,

Administration > Currency.

Do you have multiple currencies? Try to change rate to any arbitrary value and save. Then change it to proper and save again.

[bradaks]

Hello,

We do not have multiple currencies. I went ahead and added a random currency with a random rate. No luck.

Here is the currency table:


Here is the entity:

[yuri]

How did you create the currency field? If a currency field is created via Enity Manager then a currency converted field is created automatically with a proper label. Maybe the currency converted field is missing?

[bradaks]

The currency field was created in the entity manager. I do not see the converted field in the sql table but it is in the entity manager as a field.

[bradaks]

Here is what happens when I try to pull the conversion in a list report:

[yuri]

Do you have a custom Repository class for this entity type?

[yuri]

Could you provide the file custom/Espo/Custom/Resources/metadata/entityDefs/{entityType}.json

[bradaks]

{
    "fields": {
        "name": {
            "type": "varchar",
            "required": true,
            "trim": true
        },
        "description": {
            "type": "text"
        },
        "createdAt": {
            "type": "datetime",
            "readOnly": true
        },
        "modifiedAt": {
            "type": "datetime",
            "readOnly": true
        },
        "createdBy": {
            "type": "link",
            "readOnly": true,
            "view": "views/fields/user"
        },
        "modifiedBy": {
            "type": "link",
            "readOnly": true,
            "view": "views/fields/user"
        },
        "assignedUser": {
            "type": "link",
            "required": true,
            "view": "views/fields/assigned-user"
        },
        "teams": {
            "type": "linkMultiple",
            "view": "views/fields/teams"
        },
        "hourlyPay": {
            "type": "currency",
            "required": false,
            "audited": false,
            "readOnly": false,
            "tooltip": false,
            "isCustom": true
        },
        "paymentID": {
            "type": "number",
            "len": 36,
            "notNull": false,
            "unique": false,
            "nextNumber": 1,
            "padLength": 5,
            "prefix": "P-",
            "tooltip": false,
            "isCustom": true
        },
        "totalPaid": {
            "type": "currency",
            "required": false,
            "audited": false,
            "readOnly": true,
            "tooltip": false,
            "isCustom": true
        },
        "milesDriven": {
            "type": "int",
            "required": false,
            "disableFormatting": false,
            "audited": false,
            "readOnly": false,
            "tooltip": false,
            "isCustom": true
        },
        "milesPaid": {
            "type": "currency",
            "required": false,
            "audited": false,
            "readOnly": false,
            "tooltip": true,
            "isCustom": true,
            "min": null,
            "max": null,
            "tooltipText": "Default is .53"
        },
        "documents": {
            "type": "linkMultiple",
            "layoutDetailDisabled": false,
            "layoutListDisabled": true,
            "layoutMassUpdateDisabled": false,
            "importDisabled": false,
            "noLoad": false,
            "isCustom": true
        },
        "contact": {
            "type": "link",
            "required": true,
            "audited": false,
            "readOnly": false,
            "tooltip": false
        },
        "totalPaidHourly": {
            "type": "currency",
            "required": false,
            "audited": false,
            "readOnly": true,
            "tooltip": false,
            "isCustom": true
        },
        "totalPaidMileage": {
            "type": "currency",
            "required": false,
            "audited": false,
            "readOnly": true,
            "tooltip": false,
            "isCustom": true
        },
        "otherPaidFees": {
            "type": "currency",
            "required": false,
            "audited": false,
            "readOnly": false,
            "tooltip": false,
            "isCustom": true
        },
        "otherFeeJustification": {
            "type": "varchar",
            "required": false,
            "trim": true,
            "audited": false,
            "readOnly": false,
            "tooltip": false,
            "isCustom": true
        },
        "quantityHourWorked": {
            "notNull": false,
            "type": "float",
            "required": false,
            "audited": false,
            "readOnly": false,
            "tooltip": false,
            "isCustom": true
        },
        "status": {
            "type": "enum",
            "required": false,
            "options": [
                "Requires Payment",
                "Paid",
                "Payment Withdrawn Due to Viability"
            ],
            "default": "Requires Payment",
            "isSorted": false,
            "audited": true,
            "readOnly": false,
            "tooltip": false,
            "isCustom": true
        },
        "transactionCheckNumber": {
            "type": "varchar",
            "required": false,
            "trim": true,
            "audited": false,
            "readOnly": false,
            "tooltip": false,
            "isCustom": true
        },
        "internalPaymentInformation": {
            "type": "wysiwyg",
            "required": false,
            "seeMoreDisabled": false,
            "readOnly": false,
            "tooltip": false,
            "isCustom": true
        },
        "paymentCheckACH": {
            "type": "varchar",
            "required": false,
            "trim": true,
            "audited": false,
            "readOnly": false,
            "tooltip": false,
            "isCustom": true
        },
        "iOP": {
            "type": "link"
        },
        "dateOfAssignmentCompletion": {
            "notNull": false,
            "type": "date",
            "required": false,
            "audited": false,
            "readOnly": true,
            "tooltip": false,
            "isCustom": true
        },
        "uploads": {
            "layoutListDisabled": true,
            "type": "attachmentMultiple",
            "required": false,
            "sourceList": [],
            "tooltip": false,
            "isCustom": true
        },
        "assignmentAddress": {
            "skipOrmDefs": true,
            "type": "address",
            "tooltip": false,
            "isCustom": true
        },
        "assignmentAddressCity": {
            "readOnly": true,
            "required": false,
            "trim": false,
            "audited": false,
            "tooltip": false
        },
        "assignmentAddressState": {
            "readOnly": true,
            "required": false,
            "trim": false,
            "audited": false,
            "tooltip": false
        },
        "specificPaymentInformation": {
            "type": "wysiwyg",
            "required": false,
            "readOnly": true,
            "seeMoreDisabled": false,
            "tooltip": false,
            "isCustom": true
        },
        "officeName": {
            "type": "text",
            "required": false,
            "rows": 4,
            "lengthOfCut": 400,
            "readOnly": true,
            "seeMoreDisabled": false,
            "tooltip": false,
            "isCustom": true
        },
        "assignment": {
            "type": "link"
        },
        "account": {
            "type": "link"
        },
        "payrollStartDate": {
            "notNull": false,
            "type": "date",
            "required": false,
            "tooltipText": "Starts and ends on a Saturday",
            "audited": false,
            "readOnly": false,
            "tooltip": true,
            "isCustom": true
        },
        "payrollEndDate": {
            "notNull": false,
            "type": "date",
            "required": false,
            "tooltipText": "Payroll ends and begins on Saturday.",
            "audited": false,
            "readOnly": false,
            "tooltip": true,
            "isCustom": true
        }
    },
    "links": {
        "createdBy": {
            "type": "belongsTo",
            "entity": "User"
        },
        "modifiedBy": {
            "type": "belongsTo",
            "entity": "User"
        },
        "assignedUser": {
            "type": "belongsTo",
            "entity": "User"
        },
        "teams": {
            "type": "hasMany",
            "entity": "Team",
            "relationName": "EntityTeam",
            "layoutRelationshipsDisabled": true
        },
        "documents": {
            "type": "hasMany",
            "relationName": "paymentDocument",
            "foreign": "payments",
            "entity": "Document",
            "audited": false,
            "isCustom": true
        },
        "contact": {
            "type": "belongsTo",
            "foreign": "payments",
            "entity": "Contact",
            "audited": false,
            "isCustom": true
        },
        "iOP": {
            "type": "belongsTo",
            "foreign": "payments",
            "entity": "IOP",
            "audited": true,
            "isCustom": true
        },
        "assignment": {
            "type": "belongsTo",
            "foreign": "payments",
            "entity": "Assignment",
            "audited": false,
            "isCustom": true
        },
        "account": {
            "type": "belongsTo",
            "foreign": "payments",
            "entity": "Account",
            "audited": false,
            "isCustom": true
        }
    },
    "collection": {
        "sortBy": "createdAt",
        "asc": false,
        "textFilterFields": [
            "name"
        ]
    },
    "indexes": {
        "name": {
            "columns": [
                "name",
                "deleted"
            ]
        },
        "assignedUser": {
            "columns": [
                "assignedUserId",
                "deleted"
            ]
        }
    }

[yuri]

Are total_paid_currency fields have a value 'USD' in your entity table?

[bradaks]

Hello,

Nope. They are all "NULL". Not sure why that would happen. That is definitely the cause.

[yuri]

Maybe you used workflow or formula to calculate currency amount values on create.

You can populate them with the query.

UPDATE your_table name SET total_paid_currency = 'USD'

[bradaks]

Yuri,

You are 100% correct. THe field is populated by a workflow that adds all amounts into the total amount. Thanks so much for helping me figure this out.

[yuri]

So, you need to set 'USD' in your workflow action.