ArchiveRestrictions not working as expected

Hi

I am trying to get a list of documents using the FindAgent using restrictions.

1. I would like to get all documents filtered by contactId.
2. Next step is to filter the documents be date of creation or updatedDate.
3. Then i would like to have some freetext search criteria on these columns person/fullName, type, associate/fullName, subject, contact/name

Everything is working as expected until i add the freetext search criteria. Can you please tell me what i am doing wrong?

The code snippet below is what i'm using to get the list of documents.

            var restrictions = new List<ArchiveRestrictionInfo>();

            restrictions.Add(new ArchiveRestrictionInfo
            {
                Name = "contactId",
                Operator = "equals",
                InterOperator = InterRestrictionOperator.And,
                InterParenthesis = 0,
                Values = new[] { id.ToString() },
                IsActive = true
            });

            restrictions.Add(new ArchiveRestrictionInfo
            {
                Name = "date",
                Operator = "before",
                InterOperator = InterRestrictionOperator.And,
                InterParenthesis = 1,
                Values = new[] { dataTableParameters.CreatedBeforeDate }
            });

            restrictions.Add(new ArchiveRestrictionInfo
            {
                Name = "date",
                Operator = "after",
                InterOperator = InterRestrictionOperator.And,
                InterParenthesis = -1,
                Values = new[] { dataTableParameters.CreatedAfterDate }
            });

            restrictions.Add(new ArchiveRestrictionInfo
            {
                Name = "updatedDate",
                Operator = "before",
                InterOperator = InterRestrictionOperator.And,
                InterParenthesis = 1,
                Values = new[] { dataTableParameters.CreatedBeforeDate }
            });

            restrictions.Add(new ArchiveRestrictionInfo
            {
                Name = "updatedDate",
                Operator = "after",
                InterOperator = InterRestrictionOperator.And,
                InterParenthesis = -1,
                Values = new[] { dataTableParameters.CreatedAfterDate }
            });

            if (!string.IsNullOrWhiteSpace(dataTableParameters.FreeTextSearch))
            {
                restrictions.Add(new ArchiveRestrictionInfo
                {
                    Name = "person/fullName",
                    InterParenthesis = 0,
                    InterOperator = InterRestrictionOperator.Or,
                    Operator = "contains",
                    Values = new[] { dataTableParameters.FreeTextSearch }
                });

                restrictions.Add(new ArchiveRestrictionInfo
                {
                    Name = "type",
                    InterParenthesis = 0,
                    InterOperator = InterRestrictionOperator.Or,
                    Operator = "oneOf",
                    Values = new[] { dataTableParameters.FreeTextSearch }
                });

                restrictions.Add(new ArchiveRestrictionInfo
                {
                    Name = "associate/fullName",
                    InterParenthesis = 0,
                    InterOperator = InterRestrictionOperator.Or,
                    Operator = "contains",
                    Values = new[] { dataTableParameters.FreeTextSearch }
                });

                restrictions.Add(new ArchiveRestrictionInfo
                {
                    Name = "subject",
                    InterParenthesis = 0,
                    InterOperator = InterRestrictionOperator.Or,
                    Operator = "contains",
                    Values = new[] { dataTableParameters.FreeTextSearch }
                });

                restrictions.Add(new ArchiveRestrictionInfo
                {
                    Name = "contact/name",
                    InterParenthesis = 0,
                    InterOperator = InterRestrictionOperator.And,
                    Operator = "contains",
                    Values = new[] { dataTableParameters.FreeTextSearch }
                });
            }

            using (var agent = new FindAgent())
            {
                var result = agent.FindFromRestrictionsColumns(restrictions.ToArray(), "FindDocument", columns, 50, 0);

                return result;
            }

RE: ArchiveRestrictions not working as expected

Hello Michael,

when using an "OR' restriction, you will need to set all restriction parameters again, since you started a new 'group' of restrictions.

var restrictions = new List<ArchiveRestrictionInfo>();

			// group 1 
            restrictions.Add(new ArchiveRestrictionInfo
            {
                Name = "contactId",
                Operator = "equals",
                InterOperator = InterRestrictionOperator.And,
                InterParenthesis = 0,
                Values = new[] { id.ToString() },
                IsActive = true
            });

            restrictions.Add(new ArchiveRestrictionInfo
            {
                Name = "date",
                Operator = "before",
                InterOperator = InterRestrictionOperator.And,
                InterParenthesis = 1,
                Values = new[] { dataTableParameters.CreatedBeforeDate }
            });

            restrictions.Add(new ArchiveRestrictionInfo
            {
                Name = "date",
                Operator = "after",
                InterOperator = InterRestrictionOperator.And,
                InterParenthesis = -1,
                Values = new[] { dataTableParameters.CreatedAfterDate }
            });

            restrictions.Add(new ArchiveRestrictionInfo
            {
                Name = "updatedDate",
                Operator = "before",
                InterOperator = InterRestrictionOperator.And,
                InterParenthesis = 1,
                Values = new[] { dataTableParameters.CreatedBeforeDate }
            });

            restrictions.Add(new ArchiveRestrictionInfo
            {
                Name = "updatedDate",
                Operator = "after",
                InterOperator = InterRestrictionOperator.Or,
                InterParenthesis = -1,
                Values = new[] { dataTableParameters.CreatedAfterDate }
            });

	

            if (!string.IsNullOrWhiteSpace(dataTableParameters.FreeTextSearch))
            {
				
					// group 2
                restrictions.Add(new ArchiveRestrictionInfo
                {
                    Name = "person/fullName",
                    InterParenthesis = 0,
                    InterOperator = InterRestrictionOperator.And,
                    Operator = "contains",
                    Values = new[] { dataTableParameters.FreeTextSearch }
                });

				restrictions.Add(new ArchiveRestrictionInfo
				{
					Name = "contactId",
					Operator = "equals",
					InterOperator = InterRestrictionOperator.And,
					InterParenthesis = 0,
					Values = new[] { id.ToString() },
					IsActive = true
				});

				restrictions.Add(new ArchiveRestrictionInfo
				{
					Name = "date",
					Operator = "before",
					InterOperator = InterRestrictionOperator.And,
					InterParenthesis = 1,
					Values = new[] { dataTableParameters.CreatedBeforeDate }
				});

				restrictions.Add(new ArchiveRestrictionInfo
				{
					Name = "date",
					Operator = "after",
					InterOperator = InterRestrictionOperator.And,
					InterParenthesis = -1,
					Values = new[] { dataTableParameters.CreatedAfterDate }
				});

				restrictions.Add(new ArchiveRestrictionInfo
				{
					Name = "updatedDate",
					Operator = "before",
					InterOperator = InterRestrictionOperator.And,
					InterParenthesis = 1,
					Values = new[] { dataTableParameters.CreatedBeforeDate }
				});

				restrictions.Add(new ArchiveRestrictionInfo
				{
					Name = "updatedDate",
					Operator = "after",
					InterOperator = InterRestrictionOperator.Or,
					InterParenthesis = -1,
					Values = new[] { dataTableParameters.CreatedAfterDate }
				});
				
				// group 3

                restrictions.Add(new ArchiveRestrictionInfo
                {
                    Name = "type",
                    InterParenthesis = 0,
                    InterOperator = InterRestrictionOperator.And,
                    Operator = "oneOf",
                    Values = new[] { dataTableParameters.FreeTextSearch }
                });


				restrictions.Add(new ArchiveRestrictionInfo
				{
					Name = "contactId",
					Operator = "equals",
					InterOperator = InterRestrictionOperator.And,
					InterParenthesis = 0,
					Values = new[] { id.ToString() },
					IsActive = true
				});

				restrictions.Add(new ArchiveRestrictionInfo
				{
					Name = "date",
					Operator = "before",
					InterOperator = InterRestrictionOperator.And,
					InterParenthesis = 1,
					Values = new[] { dataTableParameters.CreatedBeforeDate }
				});

				restrictions.Add(new ArchiveRestrictionInfo
				{
					Name = "date",
					Operator = "after",
					InterOperator = InterRestrictionOperator.And,
					InterParenthesis = -1,
					Values = new[] { dataTableParameters.CreatedAfterDate }
				});

				restrictions.Add(new ArchiveRestrictionInfo
				{
					Name = "updatedDate",
					Operator = "before",
					InterOperator = InterRestrictionOperator.And,
					InterParenthesis = 1,
					Values = new[] { dataTableParameters.CreatedBeforeDate }
				});

				restrictions.Add(new ArchiveRestrictionInfo
				{
					Name = "updatedDate",
					Operator = "after",
					InterOperator = InterRestrictionOperator.Or,
					InterParenthesis = -1,
					Values = new[] { dataTableParameters.CreatedAfterDate }
				});
				
					// group 3 

                restrictions.Add(new ArchiveRestrictionInfo
                {
                    Name = "associate/fullName",
                    InterParenthesis = 0,
                    InterOperator = InterRestrictionOperator.And,
                    Operator = "contains",
                    Values = new[] { dataTableParameters.FreeTextSearch }
                });

				restrictions.Add(new ArchiveRestrictionInfo
				{
					Name = "contactId",
					Operator = "equals",
					InterOperator = InterRestrictionOperator.And,
					InterParenthesis = 0,
					Values = new[] { id.ToString() },
					IsActive = true
				});

				restrictions.Add(new ArchiveRestrictionInfo
				{
					Name = "date",
					Operator = "before",
					InterOperator = InterRestrictionOperator.And,
					InterParenthesis = 1,
					Values = new[] { dataTableParameters.CreatedBeforeDate }
				});

				restrictions.Add(new ArchiveRestrictionInfo
				{
					Name = "date",
					Operator = "after",
					InterOperator = InterRestrictionOperator.And,
					InterParenthesis = -1,
					Values = new[] { dataTableParameters.CreatedAfterDate }
				});

				restrictions.Add(new ArchiveRestrictionInfo
				{
					Name = "updatedDate",
					Operator = "before",
					InterOperator = InterRestrictionOperator.And,
					InterParenthesis = 1,
					Values = new[] { dataTableParameters.CreatedBeforeDate }
				});

				restrictions.Add(new ArchiveRestrictionInfo
				{
					Name = "updatedDate",
					Operator = "after",
					InterOperator = InterRestrictionOperator.Or,
					InterParenthesis = -1,
					Values = new[] { dataTableParameters.CreatedAfterDate }
				});
				
				// group 4

                restrictions.Add(new ArchiveRestrictionInfo
                {
                    Name = "subject",
                    InterParenthesis = 0,
                    InterOperator = InterRestrictionOperator.And,
                    Operator = "contains",
                    Values = new[] { dataTableParameters.FreeTextSearch }
                });

				restrictions.Add(new ArchiveRestrictionInfo
				{
					Name = "contactId",
					Operator = "equals",
					InterOperator = InterRestrictionOperator.And,
					InterParenthesis = 0,
					Values = new[] { id.ToString() },
					IsActive = true
				});

				restrictions.Add(new ArchiveRestrictionInfo
				{
					Name = "date",
					Operator = "before",
					InterOperator = InterRestrictionOperator.And,
					InterParenthesis = 1,
					Values = new[] { dataTableParameters.CreatedBeforeDate }
				});

				restrictions.Add(new ArchiveRestrictionInfo
				{
					Name = "date",
					Operator = "after",
					InterOperator = InterRestrictionOperator.And,
					InterParenthesis = -1,
					Values = new[] { dataTableParameters.CreatedAfterDate }
				});

				restrictions.Add(new ArchiveRestrictionInfo
				{
					Name = "updatedDate",
					Operator = "before",
					InterOperator = InterRestrictionOperator.And,
					InterParenthesis = 1,
					Values = new[] { dataTableParameters.CreatedBeforeDate }
				});

				restrictions.Add(new ArchiveRestrictionInfo
				{
					Name = "updatedDate",
					Operator = "after",
					InterOperator = InterRestrictionOperator.Or,
					InterParenthesis = -1,
					Values = new[] { dataTableParameters.CreatedAfterDate }
				});
				
				// group 5

                restrictions.Add(new ArchiveRestrictionInfo
                {
                    Name = "contact/name",
                    InterParenthesis = 0,
                    InterOperator = InterRestrictionOperator.And,
                    Operator = "contains",
                    Values = new[] { dataTableParameters.FreeTextSearch }
                });
				
				restrictions.Add(new ArchiveRestrictionInfo
				{
					Name = "contactId",
					Operator = "equals",
					InterOperator = InterRestrictionOperator.And,
					InterParenthesis = 0,
					Values = new[] { id.ToString() },
					IsActive = true
				});

				restrictions.Add(new ArchiveRestrictionInfo
				{
					Name = "date",
					Operator = "before",
					InterOperator = InterRestrictionOperator.And,
					InterParenthesis = 1,
					Values = new[] { dataTableParameters.CreatedBeforeDate }
				});

				restrictions.Add(new ArchiveRestrictionInfo
				{
					Name = "date",
					Operator = "after",
					InterOperator = InterRestrictionOperator.And,
					InterParenthesis = -1,
					Values = new[] { dataTableParameters.CreatedAfterDate }
				});

				restrictions.Add(new ArchiveRestrictionInfo
				{
					Name = "updatedDate",
					Operator = "before",
					InterOperator = InterRestrictionOperator.And,
					InterParenthesis = 1,
					Values = new[] { dataTableParameters.CreatedBeforeDate }
				});

				restrictions.Add(new ArchiveRestrictionInfo
				{
					Name = "updatedDate",
					Operator = "after",
					InterOperator = InterRestrictionOperator.Or,
					InterParenthesis = -1,
					Values = new[] { dataTableParameters.CreatedAfterDate }
				});
            }

            using (var agent = new FindAgent())
            {
                var result = agent.FindFromRestrictionsColumns(restrictions.ToArray(), "FindDocument", columns, 50, 0);

                return result;
            }

Note: as you can see this setup causes a lot of code duplication, better would be to maybe using the new string based syntax that you can use withe the ArchiveAgent & GetArchiveListByColumns2

Von: David Hollegien 18. Dez 2020

RE: ArchiveRestrictions not working as expected

Thanks a lot for the input!

Yeah, that's a lot of extra code added.

Unfortunately your suggestion does not seem to work out of the box.

I will look into other methods to get the documents as you suggested.

Von: Michael Jensen 18. Dez 2020

RE: ArchiveRestrictions not working as expected

I have tried to figure out how to get the documents using the ArchiveAgent. Can't really seem to find any documentation or examples showing how to provide the correct parameters for this functionality.

I have tried using the following code without any response but exceptions.

var providerName = "FindDocument";
var columns = "'documentId,date,updatedDate,person/fullName,type,associate/fullName,subject,contact/name','contactId'";
var sortOrder = "'documentId desc'";
var restictions = "'contactId = '1' and (date before '' and date after '') and (updatedDate before '' and updatedDate after '') and (person/fullName contains '' or type oneOf ('') or associate/fullName contains '' or subject contains '' or contact/name contains '')'";
var entities = "";

using (var agent = new ArchiveAgent())
{
    var result = agent.GetArchiveListByColumns2(providerName, columns, sortOrder, restictions, entities, 0, 50);
    return result;
}

Can someone point to an example or some detailed documentation?

Von: Michael Jensen 18. Dez 2020

RE: ArchiveRestrictions not working as expected

Have you tried not surrounding all of your arguments with single quotes?

Von: Véronique Borel 18. Dez 2020

RE: ArchiveRestrictions not working as expected

var providerName = "FindDocument";
var columns = "documentId,date,updatedDate,person/fullName,type,associate/fullName,subject,contact/name,contactId";
var sortOrder = "documentId desc";
var restictions = "contactId = 1 and (date before '2020-10-20' and date after '2020-12-30') and (updatedDate before '2020-10-20' and updatedDate after '2020-1-20') and (person/fullName contains 'foo' or type oneOf (1,2) or associate/fullName contains 'boo' or subject contains 'xyz' or contact/name contains 'blub')";
var entities = "";

using (var agent = new ArchiveAgent())
{
    var result = agent.GetArchiveListByColumns2(providerName, columns, sortOrder, restictions, entities, 0, 50);
    return result;
}
Von: Christian Mogensen 18. Dez 2020

RE: ArchiveRestrictions not working as expected

Thanks for the fast reply.

I don't get any documents, and i can't see what i am doing wrong.

As you can see i have cut down the restrictions to just contactId.

var providerName = "FindDocument";
var columns = "documentId,date,updatedDate,person/fullName,type,associate/fullName,subject,contact/name,contactId";
var sortOrder = "documentId desc";
var restictions = "contactId = 16"; // and (date before '2020-12-12' and date after '2020-12-1')"; // and (updatedDate before '2020-10-20' and updatedDate after '2020-1-20') and (person/fullName contains 'foo' or type oneOf (1,2) or associate/fullName contains 'boo' or subject contains 'xyz' or contact/name contains 'blub')";
var entities = "";

using (var agent = new ArchiveAgent())
{
    var result = agent.GetArchiveListByColumns2(providerName, columns, sortOrder, restictions, entities, 0, 50);
    return result;
}
Von: Michael Jensen 21. Dez 2020

RE: ArchiveRestrictions not working as expected

Using the API Playground in the documentation I get an OData response

Are you sure you have the right contact id?

And the documents are visible to you?

 

Von: Christian Mogensen 21. Dez 2020

RE: ArchiveRestrictions not working as expected

I was using the exact same contactId when using the FindAgent and i got documents in the result.

Von: Michael Jensen 21. Dez 2020

RE: ArchiveRestrictions not working as expected

When i switched the ArchiveAgent with FindAgent i got the expected results. I was not aware that it could make the diffrence.

Von: Michael Jensen 22. Dez 2020

RE: ArchiveRestrictions not working as expected

I didn't know about the "string based syntax" mentioned by David and exampled by Christian! That seems VERY usable!

Will that syntax also work with the NSArchiveAgent when using GetArchiveListByColumns or GetArchiveListByColumns2 in CRMScript? Do all Archives support that syntax?

Is this syntax documented somewhere? It would be interesting to look into that.

 

Von: Marcus Svenningsson 22. Dez 2020