DataStoreTips - Revision history

Smtxwiki at 07:06, 27 April 2023

2023-04-27T07:06:14Z

← Older revision		Revision as of 09:06, 27 April 2023
Line 53:		Line 53:
	where ContentType=2		where ContentType=2


			Build a datastore with all users that have delegates

			select Person.Firstname + ' ' + Person.Lastname as Name, CASE WHEN PersonDelegate2.Id IS NULL THEN 'No delegate' ELSE PersonDelegate2.Firstname + ' ' + PersonDelegate2.Lastname END as Delegate, CASE WHEN
			PersonDelegate.GenericName IS NULL AND PersonDelegate2.Id IS NOT NULL THEN 'All Forms' ELSE PersonDelegate.GenericName END as DelegatedForm
			from Person
			left outer join PersonDelegate on Person.id = PersonDelegate.Person_Id
			left outer join Person as PersonDelegate2 on PersonDelegate.DelegatePerson_Id = PersonDelegate2.Id

	==Working around the limit of 1000 characters in datastore columns==		==Working around the limit of 1000 characters in datastore columns==

Smtxwiki at 07:03, 27 April 2023

2023-04-27T07:03:07Z

← Older revision		Revision as of 09:03, 27 April 2023
Line 45:		Line 45:
	select InternalLabel as LabelFormulier, flEN.title as FormulierTitelEN, flNL.title as FormulierTitelNL, f.id as IDFormulier, Name as RolNaam, CASE AccessLevel		select InternalLabel as LabelFormulier, flEN.title as FormulierTitelEN, flNL.title as FormulierTitelNL, f.id as IDFormulier, Name as RolNaam, CASE AccessLevel
	WHEN 1 THEN 'Lezen'		WHEN 1 THEN 'Lezen'
	WHEN 9 THEN 'Admin'		WHEN 9 THEN 'Admin'
	END AS Rechten, IsLive from dbo.role r		END AS Rechten, IsLive from dbo.role r
	inner join roleright rr on rr.role_id = r.ID		inner join roleright rr on rr.role_id = r.ID

Smtxwiki at 07:02, 27 April 2023

2023-04-27T07:02:25Z

← Older revision		Revision as of 09:02, 27 April 2023
Line 44:		Line 44:

	select InternalLabel as LabelFormulier, flEN.title as FormulierTitelEN, flNL.title as FormulierTitelNL, f.id as IDFormulier, Name as RolNaam, CASE AccessLevel		select InternalLabel as LabelFormulier, flEN.title as FormulierTitelEN, flNL.title as FormulierTitelNL, f.id as IDFormulier, Name as RolNaam, CASE AccessLevel
	WHEN 1 THEN 'Lezen'		WHEN 1 THEN 'Lezen'
	WHEN 9 THEN 'Admin'		WHEN 9 THEN 'Admin'
	END AS Rechten, IsLive from dbo.role r		END AS Rechten, IsLive from dbo.role r
	inner join roleright rr on rr.role_id = r.ID		inner join roleright rr on rr.role_id = r.ID

Smtxwiki at 07:01, 27 April 2023

2023-04-27T07:01:53Z

← Older revision		Revision as of 09:01, 27 April 2023
Line 40:		Line 40:
	FROM [dbo].[PersonGroup] PG WHERE [Active] = 1		FROM [dbo].[PersonGroup] PG WHERE [Active] = 1


			Query to get all Roles that have access to a Form (output in Datastore)

			select InternalLabel as LabelFormulier, flEN.title as FormulierTitelEN, flNL.title as FormulierTitelNL, f.id as IDFormulier, Name as RolNaam, CASE AccessLevel
			WHEN 1 THEN 'Lezen'
			WHEN 9 THEN 'Admin'
			END AS Rechten, IsLive from dbo.role r
			inner join roleright rr on rr.role_id = r.ID
			inner join form f on f.id = Content_Id
			inner join (select * from FormLanguage where language_id = 1) flEN on flEN.Form_Id = f.id
			inner join (select * from FormLanguage where language_id = 2) flNL on flNL.Form_Id = f.id
			where ContentType=2

Smtxwiki at 13:44, 12 September 2022

2022-09-12T13:44:11Z

← Older revision		Revision as of 15:44, 12 September 2022
Line 8:		Line 8:


	~~Guery~~ to get all members from a group (GROUPNAMEHERE) by Fullname & Email		Query to get all members from a group (GROUPNAMEHERE) by Fullname & Email

	SELECT "Email", "FirstName" + ' ' + "LastName" AS FullName FROM "PersonGroupPerson", "Person" WHERE "PersonGroupId" IN (SELECT "Id" FROM "PersonGroup" WHERE "Name" = 'GROUPNAMEHERE') AND "PersonId" = "Person"."Id"		SELECT "Email", "FirstName" + ' ' + "LastName" AS FullName FROM "PersonGroupPerson", "Person" WHERE "PersonGroupId" IN (SELECT "Id" FROM "PersonGroup" WHERE "Name" = 'GROUPNAMEHERE') AND "PersonId" = "Person"."Id"

Smtxwiki at 13:44, 12 September 2022

2022-09-12T13:44:00Z

← Older revision		Revision as of 15:44, 12 September 2022
Line 2:		Line 2:

	SELECT [Person].UniqueID FROM [PersonGroupPerson], [Person] WHERE [PersonGroupPerson].[PersonId] = [Person].[Id] AND [PersonGroupPerson].[PersonGroupId] = 3		SELECT [Person].UniqueID FROM [PersonGroupPerson], [Person] WHERE [PersonGroupPerson].[PersonId] = [Person].[Id] AND [PersonGroupPerson].[PersonGroupId] = 3

			- or for Oracle --

			SELECT "Person"."UniqueID" FROM "PersonGroupPerson", "Person" WHERE "PersonGroupPerson"."PersonId" = "Person"."Id" AND "PersonGroupPerson"."PersonGroupId" = 21

Smtxwiki at 07:13, 4 March 2021

2021-03-04T07:13:07Z

← Older revision		Revision as of 09:13, 4 March 2021
Line 64:		Line 64:

	Select '<a href="https://ssp.siemens.be/Common/Search.aspx?q=' + Field1 + '" target="_blank">' + Field1 + '</a>' as		Select '<a href="https://ssp.siemens.be/Common/Search.aspx?q=' + Field1 + '" target="_blank">' + Field1 + '</a>' as


			== Send weekly report with datastore entries ==
			This can be done via an autoform, that fills in a form with for example X days back as field.
			Then in the process, a Javascript step can make a CSV file, that can then be emailed out in a next step.
			Example:
			Form: http://dev.ssp7.smt-x.com/Forms/form.aspx?adminmode=1&id=672
			Process: http://dev.ssp7.smt-x.com/Workflow/Admin/ProcessAddEdit.aspx?saved=true&genericid=374
			http://dev.ssp7.smt-x.com/Common/Admin/parameterAddEdit.aspx?genericid=595

Smtxwiki: 1 revision imported

2020-05-25T20:44:27Z

1 revision imported

← Older revision	Revision as of 22:44, 25 May 2020
(No difference)

Smtxwiki at 16:55, 5 February 2019

2019-02-05T16:55:16Z

New page

Query to get all Members of a certain Group in a Datastore

SELECT [Person].UniqueID FROM [PersonGroupPerson], [Person] WHERE [PersonGroupPerson].[PersonId] = [Person].[Id] AND [PersonGroupPerson].[PersonGroupId] = 3

Guery to get all members from a group (GROUPNAMEHERE) by Fullname & Email

SELECT "Email", "FirstName" + ' ' + "LastName" AS FullName FROM "PersonGroupPerson", "Person" WHERE "PersonGroupId" IN (SELECT "Id" FROM "PersonGroup" WHERE "Name" = 'GROUPNAMEHERE') AND "PersonId" = "Person"."Id"

Query to get all Members from multiple Groups in a Datastore (this is done by name of the group, not the IDs)

SELECT "Email" FROM "PersonGroupPerson", "Person" WHERE "PersonGroupId" IN
(SELECT "Id" FROM "PersonGroup" WHERE "Name" = 'Corp group for Security Exception' OR "Name" = 'Tire group for Security Exception' OR "Name" = 'Tech group for Security Exception' OR "Name" = 'Infosec Group for Security Exception')
AND "PersonId" = "Person"."Id"

Query to get all Members by first name & last name from ALLGroups in a Datastore

SELECT Name as GroupName, (SELECT Stuff(
(SELECT N', ' + FirstName + ' ' + LastName
FROM PersonGroupPerson, Person
WHERE Person.Id = PersonGroupPerson.PersonId AND PersonGroupId = PG.Id
FOR XML PATH(''),TYPE)
.value('text()[1]','nvarchar(max)'),1,2,N'')) AS Members
FROM [dbo].[PersonGroup] PG WHERE [Active] = 1

NOTE! when there are too many members, you will need to use field 26, as it can hold more data (see below).

then use this one:

SELECT Name as GroupName, null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,(SELECT Stuff(
(SELECT N', ' + FirstName + ' ' + LastName
FROM PersonGroupPerson, Person
WHERE Person.Id = PersonGroupPerson.PersonId AND PersonGroupId = PG.Id
FOR XML PATH(''),TYPE)
.value('text()[1]','nvarchar(max)'),1,2,N'')) AS Members
FROM [dbo].[PersonGroup] PG WHERE [Active] = 1

==Working around the limit of 1000 characters in datastore columns==
By default the columns in datastore have a limit of 1000 characters. In case you need to store more data, try to use the field mapping of the fields 26, 27, 28, 29 or 30. In the SSP database these fields are defined as CLOB (unlimited content). When defining your parameter fields, you can select in which table column the data should be stored. For large text fields, select any of the mentioned fields.

Field sizes:
Field1-25: 1000 Char
Field26: Unlimited
Field27: Unlimited
Field28: Unlimited
Field29: Unlimited
Field30: Unlimited
Field31-50: 1000 Char

== merge data from fields into another datastore==
Sometimes you get the data in from an automated source, and you can't change the structure.
But if you want to merge 2 fiels into 1, you'll need a new datastore, and make something like this:

SELECT field1 + ' / ' + field2 as [Search criteria], field3, field4, field5 FROM parameterdata WHERE parameter_id=375
-or-
SELECT field1 + ' ( ' + field2 + ' [ ' + field3 + '])' as [all data], field4, field5 as [FilterField] FROM parameterdata WHERE parameter_id=375

Another example, to create a link to a ticket (via the search page).
The ticketNr is in Field1

Select '<a href="https://ssp.siemens.be/Common/Search.aspx?q=' + Field1 + '" target="_blank">' + Field1 + '</a>' as