Requirement: User wants to report on Record producer using excel upload, with import set details like inserted, uploaded, ignored, created, created by, attachment link (to download file), unique id (from excel) status, currently this feature is not available OOTB, where data is processed in multiple stages.
OOTB:
only users with "import_admin" / Admin role can currently have access to import-set to look in to the Import details like inserted, updated, ignored and other details are not available on the import set as per the user requirement.
so we came up with the below solution.
Solution:
Creates 12 additional fields on Custom Request table to fill the data from various tables to show the view to user as few users from the client have this view available controlled by a custom role.
Step 1: Create Below fields on custom request table.
#LabelNameType
1 | Import Set Run | u_import_set_run | Reference → sys_import_set_run |
2 | Upload Started | u_upload_started | Date/Time |
3 | Upload Completed | u_upload_completed | Date/Time |
4 | Import State | u_import_state | String (40) |
5 | Total Records | u_records_total | Integer |
6 | Records Inserted | u_records_inserted | Integer |
7 | Records Updated | u_records_updated | Integer |
8 | Source Batch Number | u_source_batch_number | String (100) |
9 | Import In Progress | u_import_in_progress | True/False (Boolean) |
10 | Import Set Reference | u_import_set_ref | String (100) |
11 | Download File | u_download_file | URL |
12 | Upload Receipt Status | u_upload_receipt_status | string(50) |
Step 2: Create an on-insert BR in scoped application which glides into sys attachment with current record and if attachment is found it store the URL in a variable and in turn glides to the request table and update the attachment URL, Upload Receipt Status & Import in Progress(true) for each load.
Step 3: write the on-After transform script to update the import set reference to request table to avoid the duplicates and also update other fields like batch Id, Import in Progress to false
Step 4: write an on Complete Transform script to update the request table with inserted, ignored, updated, start time, end time from sys import set run table.
Step 5: Create a view and restrict via ALC and provide access to the group & add user to that group who can monitor the uploads.
so, the view will provide details like below.
Hope this will help someone, Thanks!
