WELCOME TO THE COMMUNITY
find what you are looking for or ask a new question
Home > Forum > Latest posts

latest posts

In reply to: Daniel Krüger (Cosmo Consult)

Hi Darek,

I don't have a suitable data at my end, so I will write this query without the option to test it.
DET_ID can be used as the "unique value" to update the rows, and DictionaryEntryExists can be used for your column.

Best regads,
Daniel

select DET_ID,
case when Exists(
select WFD_ID
from WFElements
where WFD_DTYPEID = 0 -- DICTIONARY_FORM_TYPE_ID
and WFD_AttText1 = DET_Att1-- Compare the fields
-- add an optional condition whether the dictionary entry has to be active
-- If you are working with multiple business entities and each has their own dictionary you would need another condition
) then 1
else 0
end as DictionaryEntryExists

from WFElementDetails
where DET_WFDID = CURRENT_WORKFLOW_INSTANCE

Works perfectly. Thank you

Currently I'm migrating files from SharePoint to a custom built WEBCON DMS application. Normally the Instance number is generated according to a rule which is perfect for new files. The customer wants to keep their document numbering.

For the existing files I would like to update the Instance number (Database: WFD_Signature) using a small SQL script. But is it safe and is it wise to do that? I'm aware the numbers need to be unique in the system, and I know they are unique in the old system. I will also do an extra check during migration.

MVP
In reply to: Darek

I don't have BPS 2022 yet:/
Change item list value would be ok but can I ask you for an example of the SQL query?

Let say that I have an item list with Column A and column B. I also have a dictionary with Column X.

I want "1" in column B whenever value in column A exists in column X

Hi Darek,

I don't have a suitable data at my end, so I will write this query without the option to test it.
DET_ID can be used as the "unique value" to update the rows, and DictionaryEntryExists can be used for your column.

Best regads,
Daniel

select DET_ID,
case when Exists(
select WFD_ID
from WFElements
where WFD_DTYPEID = 0 -- DICTIONARY_FORM_TYPE_ID
and WFD_AttText1 = DET_Att1-- Compare the fields
-- add an optional condition whether the dictionary entry has to be active
-- If you are working with multiple business entities and each has their own dictionary you would need another condition
) then 1
else 0
end as DictionaryEntryExists

from WFElementDetails
where DET_WFDID = CURRENT_WORKFLOW_INSTANCE

In reply to: Daniel Krüger (Cosmo Consult)

Hi Darek,

this would be easy, if you could do this check with a "wizard" step. Then you could either use:
- Change item list values, to update all rows using a SQL query
- Use the for each operator introduced with BPS 2022. With this you can select the item list and use the 'change value of single field". In the action you can select a column from the item list and in the SQL query, you can reference the value of the field you are using to verify whether the row exists.

An alternative could be to use the "Form rule invoked after callback" in the configuration tab of item list. You could use the for each row to set a column to a value. The value will be returned by a prepared business rule, which has a parameter. This parameter will be set to the column value which you are using to check whether the row exists.

I haven't tested the callback approach but it could work.

Best regards,
Daniel

I don't have BPS 2022 yet:/
Change item list value would be ok but can I ask you for an example of the SQL query?

Let say that I have an item list with Column A and column B. I also have a dictionary with Column X.

I want "1" in column B whenever value in column A exists in column X

MVP

Hi Darek,

this would be easy, if you could do this check with a "wizard" step. Then you could either use:
- Change item list values, to update all rows using a SQL query
- Use the for each operator introduced with BPS 2022. With this you can select the item list and use the 'change value of single field". In the action you can select a column from the item list and in the SQL query, you can reference the value of the field you are using to verify whether the row exists.

An alternative could be to use the "Form rule invoked after callback" in the configuration tab of item list. You could use the for each row to set a column to a value. The value will be returned by a prepared business rule, which has a parameter. This parameter will be set to the column value which you are using to check whether the row exists.

I haven't tested the callback approach but it could work.

Best regards,
Daniel

In reply to: Ingo Doerrie

Hi and thank you for your response. I have this demand in WEBCON BPS 2022.1.2.59 actually. If you can help with your SDK that would be great.

Great, still we created our SDK like for 1 attribute where we save link from api and after download link from api we just download attachement.
For sure you will need change name or ID in SDK (I don't remember exacly how it works)
If you are still interested give me your email and I can send you our SDK

MVP
In reply to: Ralf Kühne (VSB)

Hi Daniel,

if you use Jva Script for this, traceability might also become problematic. The actions for adding, removing and changing item list entries only work on a per-instance basis. If you want to add an entry "from outside", these do not work. (e.g. assume a for-ech automation)
With the SQL procedures, the changes in the item list are at least visible, even if not under the name of the executing person.
Finally, there is still the use of the WEBCON REST API ... which can also be used to create item list entries.

Best regards,

Ralf

Hi Ralf,

you have a point there. I was focused on the fact that less experienced users will read this and may do something which cause undesired side effects. On the other hand, non experienced users won't be able to use the procedures.

I'm wondering whether this would be possible with R3
From the change log:
Changed the scope of privileges and the dedicated database read
account.
The change applies to systems where a dedicated Database read account
was defined during installation (the account for which SQL COMMAND rules
and all SQL queries using Default connection are executed).
When upgrading the system to the latest version, a database user named
BPS_User will be created in all content databases with read permissions for
selected tables and views. If a BPS_User user exists in the database, their
privileges will be extended accordingly. The detailed scope of privileges
granted to BPS_User is available in the dbo.proc_GrantLowPrivileges()
stored procedure.
After upgrading the system, all queries performed so far by the system in the
context of the dedicated Database read account will be executed in the
context of BPS_User.
For newly created installations, all queries using the Default connection and
queries executed in SQL COMMAND rules will always be executed in the
context of BPS_User. It is not possible to change this behavior.

If I understand this correctly, any query will be executed with the read account and it won't be possible to execute and modification query. At least without changing the permissions of the user yourself.

Best regards,
Daniel