Contactor Jobs Completed flag

[Grace]

Hi,
My question is regarding the database itself not just the API, I hope this is fine?
Users can mark contractor jobs as completed on simPRO GUI, I am struggling to find which table is this being saved to, does anyone know? None of the columns in simpro_contractor_jobs appear to have this data saved. I need this 'completed' flag for a custom native query in BI reports.

Thanks in advance,
Grace

[robbertcha]

Hi,

simPRO will save the date for the contractor job on the database when it is marked as 'completed', so please check a field called 'complete_date' in contractor_jobs table.

[Grace]

Hi,

simPRO will save the date for the contractor job on the database when it is marked as 'completed', so please check a field called 'complete_date' in contractor_jobs table.

Thanks robbertcha for trying to help,

Looking at simpro_contractor_jobs table schema the only columns available are:
'contractor_job_id', 'date', 'contractor_id', 'contractor', 'contact', 'material_cost', 'labour_cost', 'tax_code', 'job_costcentre_id', 'job_id', 'quote_cost_centre_id', 'quote_id', 'currency', 'exchange_rate'.
The 'date' column appears to be a contractor job creation date, not a completion date.

The information schema doesn't have any table with a column called 'complete_date'.
There is 'completed_date' in simpro_jobs, but that refers to a job, not a contractor job;
simpro_task has 'date_completed', but that has no relation to simpro_contractor_jobs.

I am still very confused.

[robbertcha]

Hi,

It seems not an API-related issue, but I will talk to our engineers in other teams first and get back to your when I got the answer, thank you.

[Grace]

Hi @robbertcha,

I don't think you have any more information for me, do you?

My guess is that the simpro tables available for BI report SQL queries are not 1-2-1 copies of the underlying database, but perhaps views of the database, where not all columns are included, is my guess correct?
I see that API call do return the status of contractor jobs, so I am just trying to decide if I need to completely ditch the native queries avenue in favour of working with the API?

Thanks in advance for your time advising me.

Grace

[robbertcha]

Hi,

The tables and columns available for BI report might be different with the one using in simPRO application, so we suggest to use API in your case and you are able to grab all the data you need.