I am working on a small integration service. In order for it to works, it needs to get all the new users registered in my community since (last sync) some date. I would like to use API v2 for that, but that's not mandatory.
I expected the following query to work, but it returns "604 invalid query syntax":
SELECT id, email FROM users WHERE users.registration_data.registration_time>636650496000000000
The time above is the datetime.Ticks value from C# ( I have seen a suggestion to use the long value as a baseline). I have also tried the "2010-05-07T20:45:21.000-07:00" format instead of long.
May be I am misunderstanding something here?
Solved! Go to Solution.
registration_data.registration_time is not allowed in the 'WHERE' clause. You may have to get all users, ORDER BY registration_data.registration_time and then filter out the rows you need.
SELECT id FROM users ORDER BY registration_data.registration_time DESC
Please refer to the documentation for more information - https://community.lithium.com/t5/Developer-Documentation/bd-p/dev-doc-portal?section=commv2&collecti...
Hope this helps!
@BeckyS I am not currently an admin there, but I guess I can ask for elevation. This means that I am not really familiar with this feature.
As far as I understand, this method is not a "proper" way to automate the process of retrieving the most recent users, isn't it? I mean it is designed to be used by human through its GUI, right?
@srujanayeruvaka I might have been not clear there. Please let me try to explain better:
I am working on fully automated service. So it is preferred that the solution is meant to be used in automation. For example: calling API and retrieving JSON response is a good solution. I am currently looking into your query, but it requires manual filtering and (probably) subsequent requests in cases when there are too many recent users. So this solution is good, but not perfect.
The reports solution might work as well, given that I can setup this report manually once and then call it programmatically as needed. Is this possible? I suppose this solution involves "clicking" GUI buttons and not really suitable for automation. If this is true, then I'll stick with 1st answer.
The query I had suggested earlier will be helpful for your automated solution. You have to retrieve all users using the above API and parse the response to filter out the users who have registered after a certain date. This step of filtering is not manual; it has to be via your code.
And yes, the user report from the Admin console involves clicks and is a manual process.
Thank you @srujanayeruvaka for your reply. By manual I mean "on my side". It would've been better if API allowed it, but oh well.
Thank you form your prompt responses. I'll accept your solution and will work with it.