Databoard SQL

COMMON TABLES

Whilst there are many hundreds of tables in the WorkBook database, the following tables below will help you to get started.

ACTIVITIES

Refer to the following article.

• dbo.Act
  General table with all Activites and the corresponding settings.

• dbo.ActPhase
  Activity Phase groups

• dbo.ActVat
  Activity tax table

FINANCIALS

Refer to the following article.

• dbo.ArpAccount
  Resource table for Vendors.

• dbo.ArpVat
  Tax accounts used for Vendors

• dbo.FinAccounts
  General Ledger

• dbo.FinPosting
  Header table for Project Postings

• dbo.FinPostingEntry
  Line level table for Project Postings

• dbo.FinJournal
  Header table for Journal Postings

• dbo.FinJournalEntry
  Line level table for Journal Postings

• dbo.ProfitForecast
  Header table for Net Revenue Forecasts

• dbo.ProfitForecastData
  Line level table for Net Revenue Forecasts

TIME REGISTRATION AND CAPACITY

Refer to the following article.

• dbo.TimeReg
  Line level table for employee time registrations

• dbo.Cap
  Line level table for employee capacity. Day level.

• dbo.CapUsed
  Line level table for employee booked time. Day level.

JOB RELATED TABLES

Refer to the following article.

• dbo.Job
  Main Job List table

• dbo.RessProjekter
  Main Project List table for Jobs.

• dbo.JobInvoice
  Header level table for Job Invoices

• dbo.JobFakturaAct
  Line level table for Job Invoices

• dbo.JobPrice
  Header level table for Job Price Quotes

• dbo.JobPriceActivity
  Line level table for Job Price Quotes

• dbo.JobReq
  Header level table for Job Purchase Orders

• dbo.JobAjustment
  Header level table for Job Adjustments

RESOURCES

Refer to the following article

• dbo.Ress
  Main Resource List table, contains all resource types

• dbo.Emp
  Employee List table

• dbo.Cust
  Customer List table

HELPFUL NOTES

UAT RECOMMENDED

When testing SQL and databoards, it is always recommended to use your UAT system. Running large datasets without filtering could possibly cause speed issues for others logged in.

USE FILTERING

When testing, and especially when exploring the database, it is recommended to limit your results.

For example, limit the results to a specific or specific companies:

And/Or, a date range:

ENUMERATION TYPES

WorkBook has many different things that will use enums. For example, if you look at the dbo.Job table, you will notice Job Status Id, but no Job Status Description.

The following table holds most enums: dbo.wbEnum

A way search for the Enum required for Job Status might be:

You will see a returned table such as:

Here you can see a few things you’ll need. EnumHdrId and EnumVal

EnumVal would match the value on the job, and EnumHdrId is the header which you would want to limit to.

For an example of how to join this enum to the Job table, to list the Job Status Description by Job, you could use the following: