Skip to main content
I want to upload a contract for a employee. The file should be stored in a employee folder on the file server. The domain assigned of the file-upload column contains a predefined network path. This path should be variable depending on the employee name.



For example: I upload a contract for the employee "NVP\d.vanleeuwen". The uploaded file should be stored in the following file path: \\fileserver\file_upload\Employee\d.vanleeuwen\contract.pdf



In this case the path must be created as follows: \\fileserver\file_upload\Employee\@variable_employee_name\@uploaded_file







I've created a domain with control_id "File upload" and a file_storage_id who is linked to a storage location on our file-server (For example: \\fileserver\file_upload\Employee\).



I've already tried to write and executed the following code for the insert_trigger of the employee_contract table. Unfortunately this doesn't work because it changes the path but does not move the file.



code:
-- parameters declaren
declare @user_name varchar(100)
       ,@path      varchar(200)
       ,@file      varchar(200)
-- user name ophalen en strippen van prefix
select @user_name = replace(e.employee_user_name,'nvp\','')
from employee e
join inserted i
  on i.employee_id = e.employee_id

  -- path is vast gedefinieerd
set @path = '\\fileserver\file_upload\Employee\'

-- file geupload is variabel, bestandsnaam ophalen.
select @file = replace(ec.contract_file,'\\fileserver\file_upload\Employee','')
from employee_contract ec
join inserted i
  on i.employee_contract_id = ec.employee_contract_id
  -- alle variabelen samenvoegen tot nieuwe pad naam
update ec
set contract_file = concat(@path
                          ,@user_name
                          ,@file
                          )
from employee_contract ec
join inserted i
  on i.employee_contract_id = ec.employee_contract_id




I am looking forward to a possible solution 💡
Hey Dennis,



Maybe a process flow can help out?



With the process flow you could create the desired folder after adding a record that has a contract, then move the file from the saved place to the newly made folder and thereafter update the ec.contract_file column value.



Hope this helps you out!,

Mark Jongeling
Maybe a default on the task could help. You can rewrite the destination path before executing / uploading the file. The variable is passed with the full path, i.e. \\server\share\storage\my_file.csv.

code:
if @cursor_from_col_id = 'file_upload'
   and 
   @file_upload is not null
begin

  set @file_upload = ... -- do some rewriting.

end

Reply


Terms & ConditionsCookie settings