What Is Spire Functions?
Spire Functions lets you easily create custom functions in your Spire PostgreSQL database that represent Spire data in a format that is much easier to use. These functions can be utilized by applications other than Spire, such as Excel, MS Access, and Word, or with third party integrations such as shipping company software, EDI, and marketing services etc.
Why Use Spire Functions?
Raw Spire data can be difficult to use for the following reasons:
- Arrays. Information such as address lines, contact names, tax amounts and many others are stored in arrays. Spire Functions splits those arrays into discrete numbered fields.
- Phone numbers. These are stored in an unformatted way that is unsuitable for presentation.
- UDF Fields. ‘User Defined Field’ data is notoriously difficult to deal with. Multiple fields of different data types are jammed into a single string called JSONB. Text is sometimes contained in quotes, sometimes not; numbers are presented as text, and dates never come out as dates. Not every element is always present, and the old data from previously deleted UDF fields is still present.
- Some Spire tables store data related to completely different entities in one table. The addresses table contains all addresses in the database including customers, vendors, and employees. Spire Functions will automatically filter out the unrelated records so you can focus on the specific data you want to work with.
- Spire stores some related data in two or more tables. Retrieving data from related tables requires an understanding of the underlying data, and proficiency in database linking techniques. Not all third-party applications that could use Spire data offer the ability to link this data easily, if at all.
Who Can Use Spire Functions?
Spire Functions is designed for users with no specialized knowledge of databases or Spire internal data storage rules. It is primarily aimed at ‘power users’ already proficient in something like Excel, or system admins supporting those users. Users with the knowledge and expertise to overcome these difficulties themselves can still benefit in the following ways:
- All phone numbers, arrays and UDF fields are formatted automatically, saving time.
- If Spire adds a new field, Spire Functions can be refreshed to include them in your data.
- If Spire removes a field, your code can break. That code could be scattered across many Excel spreadsheets or other applications that rely on it. Maintaining this logic in a central place is a real time-saver.
- Created functions are centrally managed and can be made available to all users on the network. This offers a consistent view of the data to all users, and the ability to share a common data source.
What Else can Spire Functions Do?
Spire functions can implement criteria on the view of data the user gets. Users can get a view of just certain types of customers, only customers with a certain salesperson, only GL accounts within a certain range, only Quotes but not Orders, or just about any other criteria you can come up with.
You can rename generic Spire fields to match your use of those fields. So ‘cust_type’ becomes ‘cust_region’ if that’s how you use it.