DEVELOPER CENTER
Hi Dev Team,
I am having a bit of trouble finding the location of where the custom field is stored in the tables in SQL.
Could anyone point me in the right direction?
Thanks
Scott
Custom Field location in Table - SQL
Forum rules
- Check the documentation first. Before posting questions, please check the documentation to ensure it doesn't already cover what you need.
- No spam. All automated messages, advertisements, and links to competitor websites will be deleted immediately.
- Post in relevant sub-forums only. Messages posted in the wrong topic area will be removed and placed in the correct sub-forum by moderators.
- Respect other users. No flaming or abusing fellow forum members. Users who continue to post inflammatory, abusive comments will be deleted from the forum after two warnings are issued by moderators.
- Harassment. No threats or harassment of other users will be tolerated. Any instance of threatening or harassing behavior is grounds for deletion from the forums.
- Adult content. No profanity or pornography is allowed. Posts containing adult material will be deleted.
- Bandwidth. All images and signatures must be 500 x 500 pixels or smaller. Posts containing over-sized images and signatures will be removed.
- Illegal content. No re-posting of copyrighted materials or other illegal content is allowed. Any posts containing illegal content or copyrighted materials will be deleted.
-
scott.towers
- Posts: 3
- Joined: Wed May 01, 2024 12:53 am
-
SimproAPITeam
- Posts: 17
- Joined: Wed May 01, 2024 2:31 am
Re: Custom Field location in Table - SQL
Hi Scott,
The API does not allow querying into a database using SQL.
To get the custom fields use the relevant custom field endpoint. These are available for jobs, quotes and many of our other data.
Here is an example for job custom fields:
https://developer.simprogroup.com/apido ... tom-Fields
For general information on custom fields see the Simpro our documentation here:
https://helpguide.simprogroup.com/Conte ... m%20fields
Regards
The API does not allow querying into a database using SQL.
To get the custom fields use the relevant custom field endpoint. These are available for jobs, quotes and many of our other data.
Here is an example for job custom fields:
Code: Select all
/api/v1.0/companies/{companyID}/jobs/{jobID}/customFields/For general information on custom fields see the Simpro our documentation here:
https://helpguide.simprogroup.com/Conte ... m%20fields
Regards
Simpro API Team | Simpro Software Group
-
scott.towers
- Posts: 3
- Joined: Wed May 01, 2024 12:53 am
Re: Custom Field location in Table - SQL
Hi Team,
I was referred here by the helpdesk
So we have a download of the DB and its stored in SQL, not trying to use the API.
And i am trying to do a query to build more robust reporting dashboards, and im trying to figure this out.
The Jobs table seems to be a Metatable.
I have been able to get most information from the quotes table, and join others for the rest, but custom fields i just cannot find. Even using a search query:
SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME like '%custom2%'
ORDER BY TABLE_NAME
So what i am looking for, would just be where does this data live, and where, if it is a metatable, does it pull this information from?
Thanks
Scott
I was referred here by the helpdesk
So we have a download of the DB and its stored in SQL, not trying to use the API.
And i am trying to do a query to build more robust reporting dashboards, and im trying to figure this out.
The Jobs table seems to be a Metatable.
I have been able to get most information from the quotes table, and join others for the rest, but custom fields i just cannot find. Even using a search query:
SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME like '%custom2%'
ORDER BY TABLE_NAME
So what i am looking for, would just be where does this data live, and where, if it is a metatable, does it pull this information from?
Thanks
Scott
-
SimproAPITeam
- Posts: 17
- Joined: Wed May 01, 2024 2:31 am
Re: Custom Field location in Table - SQL
Hi,
Unfortunately we would not have insight into the downloaded database format or setup. Most likely the tables related to custom field would be named custom_field_xxx but you would have to look through all the tables available to find the relevant tables and how they are related with their keys.
I'm sorry that we're not able to provide more help and that you were directed to the API forum for this. I will inform internally that we don't have this information.
Regards
Unfortunately we would not have insight into the downloaded database format or setup. Most likely the tables related to custom field would be named custom_field_xxx but you would have to look through all the tables available to find the relevant tables and how they are related with their keys.
I'm sorry that we're not able to provide more help and that you were directed to the API forum for this. I will inform internally that we don't have this information.
Regards
Simpro API Team | Simpro Software Group
-
scott.towers
- Posts: 3
- Joined: Wed May 01, 2024 12:53 am
Re: Custom Field location in Table - SQL
Figured it out.
Structure:
Project_custom_fields
Project_custom_fields_list
Transactional:
Project_job_custom_fields_values
Project_quote_custom_fields_values
Project_recurring_custom_fields_values
Structure:
Project_custom_fields
Project_custom_fields_list
Transactional:
Project_job_custom_fields_values
Project_quote_custom_fields_values
Project_recurring_custom_fields_values