Please follow my discussion with Infor Support below (uploaded file contains snapshots and the WFEXTJOIN.txt file). I'm new in the community, but was not able to find answers for my questions. How do I create a custom table so I can access in Workflow Designer Rule Properties please?
[View:/cfs-file/__key/communityserver-discussions-components-files/78/Communication-with-Infor-Support-about-adding-CUDF-in-WFs.docx:320:240][View:/cfs-file/__key/communityserver-discussions-components-files/78/WFEXTJOIN.txt:320:240]
ECI 2024-09-04 09:25:08 Additional comments
Technically I got it to work in Query Tools using like match. I treated DOCUMENT_ID as primary key (it finds match "2406097" inside "W~" and "~0~1~0" of "W~2406097~0~1~0". Example query below:
SELECT WORK_ORDER.BASE_ID, WORK_ORDER.PART_ID, WORK_ORDER.PRODUCT_CODE, USER_DEF_FIELDS.PROGRAM_ID, USER_DEF_FIELDS.ID, USER_DEF_FIELDS.STRING_VAL, WORK_ORDER.INACTIVE FROM USER_DEF_FIELDS LEFT OUTER JOIN WORK_ORDER ON USER_DEF_FIELDS.DOCUMENT_ID LIKE '%'+WORK_ORDER.BASE_ID+'%' WHERE USER_DEF_FIELDS.PROGRAM_ID = 'VMMFGWIN_WO' AND USER_DEF_FIELDS.ID = '00047' AND USER_DEF_FIELDS.STRING_VAL NOT LIKE '%R%' AND USER_DEF_FIELDS.STRING_VAL NOT LIKE 'I%' AND WORK_ORDER.BASE_ID <> '' AND (WORK_ORDER.PRODUCT_CODE = 'ASSEMBLY' OR WORK_ORDER.PRODUCT_CODE = 'HARNESS') AND WORK_ORDER.BASE_ID NOT LIKE '%-%' AND WORK_ORDER.BASE_ID NOT LIKE 'RMA%'
Yes I connected to forum. I can look there.
Infor Support 2024-09-04 09:21:18 Additional comments
HI Frank, That is outside scope of Support. I only mention it because it is in the KBA that a custom table can be used, but Support does not create nor do they instruct on how to create a custom table or view. Were you able to connect with other VISUAL users at community.infor.com? Regards, Infor Support
ECI 2024-09-04 09:17:47 Additional comments
"The only way I can think of to get around this would be to use a custom table to hold the data."
How do I do that? I know enough about SQL to do SELECT, WHERE, JOIN etc. but this is next level to me.
Infor Support 2024-09-04 09:15:16 Additional comments
Hi Frank, I don't believe that you can do what you want to do, whether it is against CUSTOMER_ORDER or WORK_ORDER, because USER_DEF_FIELDS doesn't have any primary keys. There is nothing to directly tie the two tables together. The only way I can think of to get around this would be to use a custom table to hold the data. No, you cannot have two workflows against the same application. Regards, Infor Support
ECI 2024-09-04 09:12:59 Additional comments
I realized that I cannot have two workflows for same applications (WO) correct? That's why I changed this 2nd workflow from WO to CO Entry
ECI 2024-09-04 09:09:55 Additional comments
This is for a different workflow application (in this case CO Entry). The WO workflow works great now.
ECI 2024-09-04 09:08:48 Additional comments
"Are you trying to use the values from the User Defined tab on the work order?"
No. I need the Customized UDF which is in USER_DEF_FIELDS table which in PROGRAM_ID column = "VMORDENT" which DOCUMENT_TYPE_ID = "00017" to retrieve STRING_VAL = "BG", "B", "G" etc. trigger this rule.
Infor Support 2024-09-04 08:36:13 Additional comments
Frank, Can you attach a screenshot of the customizable user defined field window that you have set on work orders? Infor Support
Infor Support 2024-09-04 08:34:01 Additional comments
HI Frank, I'm sorry - I take that back. I just figured out where the USER_DEF_FIELD value gets added. Let me take another look. Regards, Infor Support
Infor Support 2024-09-04 08:31:08 Additional comments
Hi Frank, Are you trying to use the values from the User Defined tab on the work order? That would be the USER_# column from the work_order table. You shouldn't need WFTEXTJOIN for that. Data that is in the USER_DEF_FIELD table comes from customizable user defined fields, which isn't enabled on work order. I'm not sure where the Query Tool is coming in? Regards, Infor Support
ECI 2024-09-04 07:34:47 Additional comments
When I attempt to update Rule Properties with newly added UDF table and column, I don't see it in drop downs
ECI 2024-09-04 07:33:47
ECI 2024-09-04 07:31:37 Additional comments
Yes one more thing. I still need help with below please (copied from earlier response below):
I renamed the wfdata.dat file to wfdata.dat.old then created WFEXTJOIN.txt file. In Query Tool, I created a query which isolates which data I'm looking for (in this case, I want to match the WORK_ORDER.BASE_ID to USER_DEF_FIELDS.DOCUMENT_ID LIKE '%' + WORK_ORDER.BASE_ID +'%', WHERE USER_DEF_FIELDS.PROGRAM_ID = 'VMMFGWIN_WO' AND USER_DEF_FIELDS.ID = '00017' (and other refining where clauses). I want to use the USER_DEF_FIELDS.STRING_VAL from this to Rule Properties. Here's what I have so far (the Query I used and the VFEXTJOIN.txt file lines I added. And yes I did a <CR> after the last line too):
QUERY:
SELECT WORK_ORDER.BASE_ID, WORK_ORDER.PART_ID, WORK_ORDER.PRODUCT_CODE, USER_DEF_FIELDS.PROGRAM_ID, USER_DEF_FIELDS.ID, USER_DEF_FIELDS.STRING_VAL, WORK_ORDER.INACTIVE FROM USER_DEF_FIELDS LEFT OUTER JOIN WORK_ORDER ON USER_DEF_FIELDS.DOCUMENT_ID LIKE '%'+WORK_ORDER.BASE_ID+'%' WHERE USER_DEF_FIELDS.PROGRAM_ID = 'VMMFGWIN_WO' AND USER_DEF_FIELDS.ID = '00047' AND USER_DEF_FIELDS.STRING_VAL NOT LIKE '%R%' AND USER_DEF_FIELDS.STRING_VAL NOT LIKE 'I%' AND WORK_ORDER.BASE_ID <> '' AND (WORK_ORDER.PRODUCT_CODE = 'ASSEMBLY' OR WORK_ORDER.PRODUCT_CODE = 'HARNESS') AND WORK_ORDER.BASE_ID NOT LIKE '%-%' AND WORK_ORDER.BASE_ID NOT LIKE 'RMA%'
VFEXTJOIN.txt:
"USER_DEF_FIELDS","WORK_ORDER", "USER_DEF_FIELDS.PROGRAM_ID = 'VMMFGWIN_WO' AND USER_DEF_FIELDS.ID = '00047' AND USER_DEF_FIELDS.STRING_VAL NOT LIKE '%R%' AND USER_DEF_FIELDS.STRING_VAL NOT LIKE 'I%' AND WORK_ORDER.BASE_ID <> '' AND (WORK_ORDER.PRODUCT_CODE = 'ASSEMBLY' OR WORK_ORDER.PRODUCT_CODE = 'HARNESS') AND WORK_ORDER.BASE_ID NOT LIKE '%-%' AND WORK_ORDER.BASE_ID NOT LIKE 'RMA%'","'%'+WORK_ORDER.BASE_ID+'%'"
I did add the quotations, except in your KBA you did not have an example of Where clauses, so I out all where clauses in one set of quotation marks (not sure if this is correct).
Also, the wfdata.dat file keeps coming back.
No errors in the workorder.log
What am I missing here?
I even removed the Where clauses (put null "" only this time), but still did not work
My objective is to add the USER_DEF_FIELDS.STRING_VAL which equals B, BG, etc. (as shown in Query tool) so I can trigger email and assign task to Process Eng group
"USER_DEF_FIELDS","WORK_ORDER", "","'%'+WORK_ORDER.BASE_ID+'%'"
The primary key should be the WO Base ID (example "2312211") which should match like in UDF table Document ID (using same example "W~2312211~0~1~0")
