Solved

How to make a File Storage path variable?

  • 1 October 2019
  • 2 replies
  • 229 views
Dennis van Leeuwen
Rank
Userlevel 4
Badge +11
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 đŸ’¡
icon

Best answer by René W 2 October 2019, 15:14

View original
1 Attachment

2 replies

Mark Jongeling
Rank
Userlevel 7
Badge +23
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
"Wake me up before you low-code"
R
Rank
Userlevel 5
Badge +15
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