Need some help accessing item status

Hi - I’m new to Monday and am trying to use SyncHub.IO to query my data using PowerBI. It works great to have the Monday.com data in a relational format.

My issue is around the text column in ColumnValue table. I’m trying to use the text column to identify the status of items. I get a highly usable set of values most of the time. However it appears a null value in text has multiple meanings - an item that has not been started yet and an item that was deleted. Do I have that correct? I need to know what has not been started vs. what is has been deleted. I looked at the IsDeleted field that that doesn’t seem to help.

Thanks

Hey Bryan!

Sounds like SyncHub.IO translates each field in our GraphQL schema into it’s own DB table. Is that correct?

By that logic, “text column in the columnValue table” means the text representation of a status column’s value. Am I on the right track?

If my assumptions are correct, then seeing the value “” or null means that the status column is blank. You can cross-reference the item in the UI, and it should look something like this:
image

This doesn’t necessarily mean the item has been deleted, simply that it doesn’t have a status (either it hasn’t been started or it isn’t relevant yet).

Let me know if that solves your issue!

Thanks!! That does explain and is consistent with what I am seeing.

Do I need to look into ActivityLog to see if this column has had any activity? Any advice how to navigate this data to find if a column value has been changed manually? I see an event of 'Update Column Value" under a pulse event but not under a board event.

PS - Synchub.io pulls data from your APIs and puts the data into a set of tables in Azure or wherever you want. The big difference between their solution and many others on the market is they put the data into a managed data warehouse they manage in the cloud. They handle the ETL and operations of keeping the data up to date. All I need to do is to write the SQL against that SQL platform and connect PowerBI. Its slick!!

Whoa, that’s really cool!

Yes, lookup the item in the Activity Log to understand the history of changes. You can use this if you want to be 100% sure that an item is untouched (as opposed to was manually marked with the empty status).

Pulse/item events are things like changing column values, creating new items, etc. Board events are more about the board structure, such as adding a new column to a board.

[BTW, pulses and items are different lingo for the same thing]

re: Synchub.io - I thought so too!!

So pulseid = itemid??? That clears lots up. Can I use that as a join value?

To find if a columnvalue has been changed do i need a query where entity = ‘pulse’ and event = ’ update_column_value’ and boardid = (the board I am looking for). Do I have to parse out the Data column to get to the pulseid to match to the itemid or is there a better way to find the changes to a columnvalue?? Parsing in SQL sucks.

Hi @BRockoff,

Yes indeed-- pulse ID is the same as item ID, back before items were called items, they were pulses!

Regarding “filtering” through your activity logs, these are the arguments that our activity logs queries currently support:

As such, you can definitely utilize the item_ids and column_ids arguments to narrow down the list, but I do think there is an inevitable amount of parsing out the data returned to get specific event information.

For board ID, you can actually nest activity logs within a single board, so you don’t need to include an argument for board ID if you choose to implement your query this way.

Does this help?