Technical Latest content Contact forms Forums Blogs Resources

ArchiveAgent, getting the email address of ticket person

lock
push_pin
done
Answered
4
9

I need to find the email, personId, and ticketId of all tickets closed before a specific date. Is this possible in a singe query?

 

I have tried the following, but no column data for email.

 var ticketsWithOwner = await archiveAgent.GetArchiveListByColumnsAsync("Dynamic", new[] { "ticket.id", "ticket.cust_id","ticket.person.email.email_address" }, null,
                new[]
                {
                    new ArchiveRestrictionInfo()
                    {
                        Name = "ticket.closed_at",
                        Operator = "Before",
                        Values = new[] { "2020-11-05" },
                        IsActive = true,
                        InterOperator = InterRestrictionOperator.And,
                    }
                },
                new[] { "ticketdisplayclosed" },
                0,
                10);

SG
Snorre Garmann
8 Nov 2023 | 07:03 AM

All Replies (9)

Try 'ticket.cust_id.(email->person_id).email_address' as column for the email address

David Hollegien
8 Nov 2023 | 08:05 AM
Thanks, just what I needed!
It would be very convenient if this join syntax was mentioned somewhere central in the docs, perhaps in the Search API pages.

SG
Snorre Garmann
8 Nov 2023 | 08:08 AM

It is documented here The Dynamic Provider - the dot syntax

David Hollegien
8 Nov 2023 | 08:09 AM

Hi,

Found this which migt be useful

Dot-syntax | SuperOffice Docs

Best regards Patrik

Patrik Lindholm
8 Nov 2023 | 08:21 AM

Hi,

ticket.cust_id will be the person_id, so I believe the fields "ticket.id", "ticket.cust_id" and "ticket.cust_id.emailAddress" will be correct.

Sverre

Sverre Hjelm
8 Nov 2023 | 08:39 AM
So, just to add a note. David's answer is correct, but there is a shortcut (a virtual field in the Dynamic provider), person.emailAddress, which returns the primary email address. Solves the problem of email_address table having multiple rows per person, and you having to get the first one.
Sverre Hjelm
8 Nov 2023 | 08:40 AM
Sverre:
I assume that everything boils down to a dynamic SQL-query in the end.

Is it somehow possible to see the actual result of the query, to be able to verify that resulting joins are actually executed as intended? In some cases I have felt the need of being able to verify the results of more complex queries, as it haven't given me the results that I've expected.

The CRMScript SearchEngine-object has the old "show as sql"-feature , that often worked good enough to get some insight. But I suppose that there isn't anything comparable for the NSObjects in CRMScript at least?

Is there any type of logs that could show these kind of data? Maybe like temporarily enable some kind of NetServer-log (in Online probably just available for SO-consultants that has access to OC)?

One potential but tedious way is to do the same query in an Onsite-VM-environment and monitor the SQL Server queries made.
Marcus Svenningsson
8 Nov 2023 | 02:52 PM

Hi Marcus,

As far as I know, there isn't any way to get the actual query from this, except as you suggest: having an onsite-VM and see what we finally query to the database. I am also quite certain that this is a functionality that will not be implemented, since the "ideal" of RnD is to keep a certain distance between users/conultants and the database. If you are able to reproduce queries that you believe are incorrect, we will absolutely appreciate the feedback, so that we can fix any such bug.

Sverre

Sverre Hjelm
8 Nov 2023 | 03:12 PM
Ok, thanks for the input!

In most cases it has probably not been a faulty behaviour from the API, but rather a lack of understanding from my side when it comes to certain implicit joins (or lack of docs ;)). So joins may have been made on another primary key than I might have assumed or something like that. For example if there are two different personId's in the same PK-table or something like that. It was a while ago a had such a problem, so don't remember exactly. But I know that I would have appreciated a way of seeing the end result of the query to verify that it actually queried what I was expecting.

I understand the ideal of keeping a healthy distance from the database, especially from a write-perspective. But from a pure dev-view-perspective it would still have been a valuable feature. But we will probably have to solve that in other ways if needed then. :)

/Marcus
Marcus Svenningsson
9 Nov 2023 | 01:46 PM

Add reply