CUSTOMISED USER DEFINED FIELDS AND CRYSTAL

Hello Everyone

Has anybody else tried to use Customised User Defined Fields in Crystal? Can anyone tell me how to select particular UDF fields in a Crystal Reports?

I'm afraid I can't use Gupta Reports because I am creating a Commercial Invoice which needs to show more than one packlist at once.

Because of the lack of fields in Visual, I have created two UDF strings in Customer Maintenance to show a customer EORI number and shipping contact details. I need to use both fields on the Commercial Invoice but Crystal can only see the first UDF string.

I have tried an If, then statement using the ID field because I can see that each field as a different ID.

Can anyone help?

Kind Regards

Elaine Slater

GMT20200514-185124_MT-Cloud-S.m4a

Find more posts tagged with

VISUAL - Enterprise General Discussions

Accepted answers

All comments

unknown

Yes, user UDF all the time in Crystal.

The data is in USER_DEF_FIELDS, you will need to select the correct ID from this table (e.g. UDF-0000105 etc) then link the records using the DOCUMENT_ID field in this table also.

If you want to select 2 UDF fields you can either, add the table twice in the visual linking expert, the second will need to have an alias (just a -1 for example) you can then treat these as two separate tables.

You may have an issue if the fields are NULL, if so you can overcome this by adding the fields you want as sub-reports and link them via the DOCUMENT_ID FIELD.

chandler

In the USER_DEF_FIELDS table, PROGRAM_ID refers to the program VMCUSMNT and the ID identifies which UDF field you are using. The DOUCUMENT_ID field identifies which customer you are looking for so probably just a CUSTOMER_ID but I don't have any on customer maintenance so I'm not sure. I know in mfg window, it does something like 'type~base~lot~split~sub' to identify it. Then you have a string, number, boolean, or date value depending on what you made the field. So, something like this should work:

SELECT
A.NUMBER_VAL AS "EORI NUMBER",
   B.STRING_VAL AS "CONTACT DETAILS"
FROM          USER_DEF_FIELDS A
LEFT JOIN   USER_DEF_FIELDS B ON <-- LEFT JOIN IF YOU'RE NOT SURE IF THERE WILL ALWAYS BE DATA IN BOTH
   B.PROGRAM_ID = A.PROGRAM_ID AND
   B.DOCUMENT_ID = A.DOCUMENT_ID
WHERE    A.PROGRAM_ID = 'VMCUSMNT'
AND          A.DOCUMENT_ID = '<CUSTOMER_ID>' <-- NOT SURE WHAT THE DOC ID LOOKS LIKE
AND          A.ID = 'UDF-0000001'  <-- WHATEVER YOUR UDF_ID'S ARE
AND          B.ID = 'UDF-0000002';

chorvath

Hi Elaine,

I'm not exactly sure why you can't see both strings but I create SQL views for specific UDFs to simplify usage in Crystal Reports....then just add the view to

See example view design below - simple but effective.

SELECT DOCUMENT_ID, STRING_VAL
FROM dbo.USER_DEF_FIELDS
WHERE (ID = '00002') AND (DOCUMENT_ID IS NOT NULL)

unknown

Thank you everyone for your replies and help. I managed to create 2 new views (one for each UDF) and I have successfully displayed them in my Crystal Commercial Invoice.

Copyright © 2025 Infor. All rights reserved.