Query to return all items, and subitems, with the status, start data, end date, due date

Hi @NathanielB, welcome to our community!

Sounds like you’re looking to only return the items with a certain status label, start date, end date, and due date right?

If so, I’m afraid that our items_by_column_values() method currently only allows filtering by a single value from a single column at this time. However, this is something that our developers are looking to implement soon! I’m happy to keep you in the loop and update here once the feature has been released.

For now, I would recommend querying all of the items by a single attribute (i.e. the status label, then the due date, etc) and then looking for overlaps in your own code backend.

Regarding your second question, you are able to query the column_values of your items to return a JSON string that contains the start and end date data of your dates. However, the parsing of this data will need to be done by you in your own code.

For instance, this string is what would be returned should you query data from one of your timeline columns:

You would then need to parse through this returned data to separate the “from” and the “to” dates. Hope this helps!

Hi Helen, thanks for the warm welcome to the community this is good feedback especially the second part however I don’t need certain status labels, start date, end date and due dates I literally want all projects, all items, all sub items and the start date, end dates and due dates for everything.

items_by_column_values does not seem appropriate as it’s for filtering?

To give you context I want to create a report that allows our customers to see the all items, sub-items, their status, start date, end date and due date.

Thanks,

Nathaniel

Got it! Thanks for clarifying.

In this case, you can include all of the column IDs for the column_values you’re looking for by inserting them in an array. Here is an example of what this query would look like:

One thing to keep in mind is that because you’re looking to retrieve a lot of data, you will most likely run into a complexity issue and will need to implement pagination and limiting. For more information, check out these threads:

• Complexity Field Weights
• Rate Limits in our API documentation
• How to implement pagination

Hi thanks for this Helen. So I’ve had a look around and exporting all account data is exactly the output that what we need. Is there anyway to automate this process to make it less manual? Do you have any tips on how customers may have done it in the past?

Hi @NathanielB,

Great question. In this case, I think it would make the most sense if you or your development team wrote a script or something that could just export all of your account data every “X” time period. This might make the most sense. Of course, this would require some technical experience. Sadly, I don’t have an example of such a script.

Additionally, while this option isn’t less manual, you can also utilize the export account data option available for admin users. This would export your account data into a .zip file. However, this would need to be done manually, ever “X” time period. If you’re curious, here’s how to do this: How to Export Your Entire Account’s Data.

For a more automated version though, I recommend going with the script option!

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.