+2
Is it possible to bulk upload attachments for a particular screen, but for multiple records.
Example -
IFS Screen - Inventory Part
No of records - 200
No of Documents - 400
Need to attach a set of 2 documents to each Inventory Part.
Can that be achieved through a script after placing all the files at a specific location ?
Best answer by Mathias Dahl
View original
+30
Hi,
This can be done in many different ways. For example, you can build a client or integration that does this using our APIs, but I think you want something that is as simple as possible.
The simplest way can think of that does not require that much work would be something like this:
1. Arrange things such that all files has the inventory part number somewhere in the file name
2. Import all files in the File Import screen.
3. Write a SQL script that loops over all the newly imported documents, extracting the inventory part number and connecting the document to the right part. Perhaps also clear the inventory part number from the document title now.
It takes some knowledge in SQL, PL/SQL and IFS' data model, of course, and access to the database from SQL*Plus or a similar tool.
Or, now that I think of it, perhaps a custom event can do it too. As each new document is imported, a custom event could trigger, doing what the script above would do (connecting the document to a part).
Good luck!
/Mathias
+30
Hi,
I decided to give this a go, to test my theory. Seems this approach works well.
I hope this might help others who need to do similar things in relation to importing documents.
The building blocks are:
- A custom event that fires on inserts/new records in the EdmFile LU (table = edm_file_tab) and where we make four of the keys available for event actions.
- An event action connected to the custom event, of type Execute Online SQL.
- The File Import screen. Each file that is imported must have the part number in parentheses, like this: My document (12345).docx. Here, 12345 would be the part number used when connecting the document. The site (see CONTRACT= in the code below) is hard coded, and probably can be in most cases. The LU, InventoryPart is also hard coded.
Here is the code for the event action:
DECLARE
doc_class_ doc_issue_tab.doc_class%TYPE := '&NEW:DOC_CLASS';
doc_no_ doc_issue_tab.doc_no%TYPE := '&NEW:DOC_NO';
doc_sheet_ doc_issue_tab.doc_sheet%TYPE := '&NEW:DOC_SHEET';
doc_rev_ doc_issue_tab.doc_rev%TYPE := '&NEW:DOC_REV';
title_ doc_title_tab.title%TYPE;
file_name_ VARCHAR2(100);
part_no_ VARCHAR2(100);
FUNCTION Get_File_Name RETURN VARCHAR2 IS
BEGIN
RETURN REGEXP_SUBSTR (title_, 'File ([A-Za-z0-9() ]+) imported', 1, 1, NULL, 1);
END Get_File_Name;
FUNCTION Is_Imported RETURN BOOLEAN IS
BEGIN
IF title_ LIKE 'File%imported' THEN
RETURN TRUE;
END IF;
RETURN FALSE;
END Is_Imported;
FUNCTION Get_Part_No RETURN VARCHAR2 IS
BEGIN
RETURN REGEXP_SUBSTR (file_name_, '\(([0-9]+)\)', 1, 1, NULL, 1);
END Get_Part_No;
PROCEDURE Log (string_ IN VARCHAR2)
IS
BEGIN
DBMS_OUTPUT.Put_Line (string_);
END Log;
PROCEDURE Get_Title IS
BEGIN
SELECT title INTO title_
FROM doc_title_tab
WHERE doc_no = doc_no_;
END Get_Title;
BEGIN
Get_Title;
IF Is_Imported THEN
Log ('imported document found');
file_name_ := Get_File_Name;
Log (' file_name_ = ' || file_name_);
part_no_ := Get_Part_No;
Log (' part_no_ = ' || part_no_);
IF part_no_ IS NOT NULL THEN
Doc_Reference_Object_API.Create_New_Reference(doc_class_, doc_no_, doc_sheet_, doc_rev_, 'InventoryPart', 'CONTRACT=70^PART_NO=' || part_no_ || '^');
END IF;
END IF;
END;Again, above the site is hard coded (CONTRACT=70). Modify it to match your setup. For other LUs you need to figure out the key ref format yourself. If you decide to put the part number using some other "format" of the file name, you need to modify the Get_Part_No function above to match that. Right now, the part number is supposed to be a number, with or without hyphens in it (12345 and 12-34-5 are supported.)
I have tried to place most of the logic in the code behind helper procedures or functions to make the main block of code as easy to understand as possible.
Good luck!
/Mathias
+2
Dear Mathias,
Thanks a lot for the guidance. It worked though after some modifications.
But all thanks to you! I took reference from your idea of using File Import feature.
I used it to attached multiple documents to multiple Employees under Employee Files screen.
The steps which I followed :-
===========
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
doc_class_ doc_issue_tab.doc_class%TYPE := '&NEW:DOC_CLASS';
doc_no_ doc_issue_tab.doc_no%TYPE := '&NEW:DOC_NO';
doc_sheet_ doc_issue_tab.doc_sheet%TYPE := '&NEW:DOC_SHEET';
doc_rev_ doc_issue_tab.doc_rev%TYPE := '&NEW:DOC_REV';
CURSOR get_data IS
SELECT
S.DOC_CLASS,
S.DOC_NO,
S.DOC_SHEET,
S.DOC_REV,
REGEXP_SUBSTR (S.TITLE, '\(([0-9]+)\)', 1, 1, NULL, 1) emp_no
FROM IFSAPP.DOC_ISSUE_REFERENCE s
WHERE S.DOC_CLASS = doc_class_
AND S.DOC_NO = doc_no_
AND S.DOC_SHEET = doc_sheet_
AND S.DOC_REV = doc_rev_
;
BEGIN
FOR i in get_data
LOOP
BEGIN
SAVEPOINT A;
IFSAPP.DOC_REFERENCE_OBJECT_API.Create_New_Reference(i.doc_class, i.doc_no, i.doc_sheet, i.doc_rev, 'CompanyPerson', 'COMPANY_ID=100^EMP_NO=' || i.emp_no || '^');
COMMIT;
doc_class_ := NULL ;
doc_no_ := NULL ;
doc_sheet_ := NULL ;
doc_rev_ := NULL ;
EXCEPTION
WHEN others THEN
ROLLBACK to A;
END;
COMMIT;
END LOOP;
END;
====
Is totally solved my purpose.
Thanks a lot again !!!!
+2
Hi,
I decided to give this a go, to test my theory. Seems this approach works well.
I hope this might help others who need to do similar things in relation to importing documents.
The building blocks are:
- A custom event that fires on inserts/new records in the EdmFile LU (table = edm_file_tab) and where we make four of the keys available for event actions.
- An event action connected to the custom event, of type Execute Online SQL.
- The File Import screen. Each file that is imported must have the part number in parentheses, like this: My document (12345).docx. Here, 12345 would be the part number used when connecting the document. The site (see CONTRACT= in the code below) is hard coded, and probably can be in most cases. The LU, InventoryPart is also hard coded.
Here is the code for the event action:
DECLARE
doc_class_ doc_issue_tab.doc_class%TYPE := '&NEW:DOC_CLASS';
doc_no_ doc_issue_tab.doc_no%TYPE := '&NEW:DOC_NO';
doc_sheet_ doc_issue_tab.doc_sheet%TYPE := '&NEW:DOC_SHEET';
doc_rev_ doc_issue_tab.doc_rev%TYPE := '&NEW:DOC_REV';
title_ doc_title_tab.title%TYPE;
file_name_ VARCHAR2(100);
part_no_ VARCHAR2(100);
FUNCTION Get_File_Name RETURN VARCHAR2 IS
BEGIN
RETURN REGEXP_SUBSTR (title_, 'File ([A-Za-z0-9() ]+) imported', 1, 1, NULL, 1);
END Get_File_Name;
FUNCTION Is_Imported RETURN BOOLEAN IS
BEGIN
IF title_ LIKE 'File%imported' THEN
RETURN TRUE;
END IF;
RETURN FALSE;
END Is_Imported;
FUNCTION Get_Part_No RETURN VARCHAR2 IS
BEGIN
RETURN REGEXP_SUBSTR (file_name_, '\(([0-9]+)\)', 1, 1, NULL, 1);
END Get_Part_No;
PROCEDURE Log (string_ IN VARCHAR2)
IS
BEGIN
DBMS_OUTPUT.Put_Line (string_);
END Log;
PROCEDURE Get_Title IS
BEGIN
SELECT title INTO title_
FROM doc_title_tab
WHERE doc_no = doc_no_;
END Get_Title;
BEGIN
Get_Title;
IF Is_Imported THEN
Log ('imported document found');
file_name_ := Get_File_Name;
Log (' file_name_ = ' || file_name_);
part_no_ := Get_Part_No;
Log (' part_no_ = ' || part_no_);
IF part_no_ IS NOT NULL THEN
Doc_Reference_Object_API.Create_New_Reference(doc_class_, doc_no_, doc_sheet_, doc_rev_, 'InventoryPart', 'CONTRACT=70^PART_NO=' || part_no_ || '^');
END IF;
END IF;
END;Again, above the site is hard coded (CONTRACT=70). Modify it to match your setup. For other LUs you need to figure out the key ref format yourself. If you decide to put the part number using some other "format" of the file name, you need to modify the Get_Part_No function above to match that. Right now, the part number is supposed to be a number, with or without hyphens in it (12345 and 12-34-5 are supported.)
I have tried to place most of the logic in the code behind helper procedures or functions to make the main block of code as easy to understand as possible.
Good luck!
/Mathias
Dear Mathias,
Thanks a lot for the guidance. It worked though after some modifications.
But all thanks to you! I took reference from your idea of using File Import feature.
I used it to attached multiple documents to multiple Employees under Employee Files screen.
The steps which I followed :-
===========
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
doc_class_ doc_issue_tab.doc_class%TYPE := '&NEW:DOC_CLASS';
doc_no_ doc_issue_tab.doc_no%TYPE := '&NEW:DOC_NO';
doc_sheet_ doc_issue_tab.doc_sheet%TYPE := '&NEW:DOC_SHEET';
doc_rev_ doc_issue_tab.doc_rev%TYPE := '&NEW:DOC_REV';
CURSOR get_data IS
SELECT
S.DOC_CLASS,
S.DOC_NO,
S.DOC_SHEET,
S.DOC_REV,
REGEXP_SUBSTR (S.TITLE, '\(([0-9]+)\)', 1, 1, NULL, 1) emp_no
FROM IFSAPP.DOC_ISSUE_REFERENCE s
WHERE S.DOC_CLASS = doc_class_
AND S.DOC_NO = doc_no_
AND S.DOC_SHEET = doc_sheet_
AND S.DOC_REV = doc_rev_
;
BEGIN
FOR i in get_data
LOOP
BEGIN
SAVEPOINT A;
IFSAPP.DOC_REFERENCE_OBJECT_API.Create_New_Reference(i.doc_class, i.doc_no, i.doc_sheet, i.doc_rev, 'CompanyPerson', 'COMPANY_ID=100^EMP_NO=' || i.emp_no || '^');
COMMIT;
doc_class_ := NULL ;
doc_no_ := NULL ;
doc_sheet_ := NULL ;
doc_rev_ := NULL ;
EXCEPTION
WHEN others THEN
ROLLBACK to A;
END;
COMMIT;
END LOOP;
END;
====
Is totally solved my purpose.
Thanks a lot again !!!!
Enter your username or e-mail address. We'll send you an e-mail with instructions to reset your password.