Unexpected formatting on Entity Custom Data

V5 Question
22

I’m not sure where the error could be honestly… The zip+4 is parsed from the ID and stored in a program setting. The program setting is reflected correctly on the entity screen. When the entity is created via the create entity action using the program setting to set the zip it gets converted to this weird date format(see above for explanation). Unless I’m missing something I’m not sure where there could be an error. I mean I could go and manually type in each zip code for each of our members and skip the automation but that takes time and adds confusion to our on-boarding process.

Hopefully all that made sense. I’ve been working on this project for so long I’m crosseyed. haha

1 Like
23

Then let me propose another solution. Try my previous and my next suggestion. However, if you are going to run Gql commands, be sure to restart the Message Server after each change you make.

String - Simple - Edit Format 00000\-0000

image
image910×377 17.2 KB

When you implement it this way, the values will be processed differently in the database, but you will be able to see the correct values.

You can see the differences between my two suggestions.

image
image910×212 3.54 KB

This is how it will look like in SambaPOS

image
image2137×585 40.4 KB

I hope you can solve your problem in this way.

24

No formatting changes, field type changes will solve this. It’s something that needs to be addressed in the source code.

Here’s a stored procedure you can call to update custom entity fields:

/* Adds or Updates Entity Custom Data */

IF OBJECT_ID('dbo.p_AddOrUpdateEntityCustomField') IS NOT NULL
  DROP PROCEDURE dbo.p_AddOrUpdateEntityCustomField;
GO

CREATE PROCEDURE dbo.p_AddOrUpdateEntityCustomField
  @EntityName NVARCHAR(MAX),
  @CustomFieldName NVARCHAR(MAX),
  @NewCustomFieldValue NVARCHAR(MAX)
AS
  BEGIN
    SET NOCOUNT ON;

    DECLARE @EntityId INT;
    DECLARE @CustomData NVARCHAR(MAX);

    SELECT @EntityId   = Id,
           @CustomData = CustomData
        FROM dbo.Entities
        WHERE Name = @EntityName;

    IF (@EntityId IS NOT NULL AND @CustomData IS NOT NULL)
      BEGIN
        IF NOT EXISTS ( SELECT 1
                            FROM OPENJSON(@CustomData)
                            WHERE JSON_VALUE(value, '$.Name') = @CustomFieldName)
          BEGIN
            SET @CustomData =
              JSON_MODIFY(
                @CustomData,
                'append $',
                JSON_QUERY('{"Name":"' + @CustomFieldName + '","Value":"' + @NewCustomFieldValue + '"}'));
          END;
        ELSE
          BEGIN
            SET @CustomData =
              JSON_MODIFY(
                @CustomData,
                '$[' + ( SELECT [key]
                             FROM OPENJSON(@CustomData)
                             WHERE JSON_VALUE(value, '$.Name') = @CustomFieldName) + '].Value',
                @NewCustomFieldValue);
          END;

        UPDATE dbo.Entities
            SET CustomData = @CustomData
            WHERE Id = @EntityId;
      END;
  END;

script to call sp:

function updateField(entityName,fieldName,fieldValue)
{
    var dml = "dbo.p_AddOrUpdateEntityCustomField '" + entityName + "', '" + fieldName + "', '" + fieldValue + "'";
    
    sql.Query(dml).First;
}
2 Likes
25

@Black

I’m sorry Black -

I’m getting the same results with each of your suggestions. Are you manually creating the entity and then typing the info into the custom entity data or are you creating the entity with the Create Entity Action?

26

Memo to the rescue again. lol Someone needs to buy this man a car! Is it in the budget? :smile:

1 Like