Transcript Sage300CRE_DatabaseFundamentals-Part1 (C

Database Fundamentals – Part 1
Understanding the Sage 300 Construction and Real Estate
Core Accounting Database
CPE Credit
• In order to receive CPE credit for this session, you must be
present for the entire session.
–
–
–
–
Session Code: C-0634
Recommended CPE Credit = 1.5
Delivery Method = Group Live
Field of Study = Specialized Knowledge and Applications
• Visit the Sage Summit Connect kiosks to enter CPE credit
during the conference.
Introduction
• Presenters
– James Coyle,
• Co-Founder & President, Event 1 Software
– Michael Newland,
• Co-Founder & Vice Present Product Management, Event 1 Software
• This presentation will be available on Sage Summit Virtual
Totebag
• Follow Sage on Twitter: @Sage_Summit
– Use the official hashtag: #SageSummit
This Session – Approach
Tools We’ll
Use
Sage 300 Construction
& Real Estate
Hands-On
Goals
Techniques to explore
your database
• Report Designer
• Office Connector
Other Tools
• Event 1 Quick Aim
• Microsoft Access
• Crystal Reports
Discovery of where
information lives
This Session – Agenda
• Database Concepts
– Exercise #1 – Data Exploration
• Files & File Names
– Exercise #2 – Observing Data Flow
• Names, Other Names, & More Names
• Data Security
Concepts
Database (tables)
How your data is
organized
Program Logic
The software
itself, containing
all of the
business logic
and processes.
User Interface
Screens & menus that you
use every day to carry out
your tasks.
Concepts – All data is stored in tables
Concepts – Tables
Columns (fields)
Rows
(Records)
Concepts – Tables, Columns, Records, Fields
Concepts – Understanding Primary Keys
Vendor IDSome
is theprimary
unique keys
identifier.
use more
No two
than
are
one
thecolumn
same!
Concepts – Using Primary Keys
Relating tables in a report or query
Foreign Key:
Columns that
contain the
values of
primary key
columns in a
related table.

Concepts – Using Primary Keys
When will I use them?
Report
Designer
• Lookup
function
Office
Connector
• TSLookup
function
• SQL
Queries –
Table Joins
Other Tools
• SQL
Queries –
Table Joins
Concepts – Recognizing Key Fields
Report
Designer
Office
Connector
• Standard
Order #1
• Key
Symbol 
Quick Aim
Other Tools
• Key
Symbol 
• Standard
Order #1
• Key
Symbol 
• Bold
Exercise #1 – Data Exploration
1. Enter a commitment
2. Print (to screen) the tables and columns (fields)
related to the JC Commitment screen.
3. Explore the data
4. Identify the primary keys
5. Optional - Identify related tables
Exercise #1 – Data Exploration
Table Information
Table name, file type, and whether
records can be create via ODBC (e.g., via
Office Connector Write)
Exercise #1 – Data Exploration
Field Name
Name you normally see in Sage 300
Construction & Real Estate.
Exercise #1 – Data Exploration
Internal Name
Name that never changes and is used
internally by Sage 300 Construction &
Real Estate as well as by Office
Connector.
Exercise #1 – Data Exploration
Field Type
The type of data the field stores (text,
number, date, yes/no)
Exercise #1 – Data Exploration
Length
Maximum number of characters or digits
that can be stored.
Exercise #1 – Data Exploration
Code
Appears with all customizable columns
and all primary key columns. For primary
keys, provides a means to identify Foreign
Keys (related tables).
Exercise #1 – Data Exploration
Replaceable
Can be updated via a program that uses
ODBC to update information, such as
Office Connector Write.
Exercise #1 – Data Exploration
Special Values
For “Option Button” and “List Box”
columns, provides a list of the possible
values.
Exercise #1 – Data Exploration
Primary Key
Columns identified for standard order #1
make up the primary key for the table.
Files & File Names – What Are They?
• NOT actual files (anymore)
• Today, they represent logical groupings of
related tables
• Stored and managed as collections of files
by the Pervasive database server
Files & File Names – Naming Convention
File Names
MASTER
The name
tells you
something
about how the
file is used by
the software.
.
J C M
•File Type
•T = Transaction
•M = Master
•S = System/Standard
•Z = Issues
•I = Invoice
•Two-Letter Application
•(AP, GL, JC, etc)
Files & File Names - Types
Master Files
• Setup Records
• Settings
• Accumulated
Totals
• Custom Fields
Examples
•
•
•
•
MASTER.GLM
MASTER.APM
MASTER.JCM
MASTER.PRM
Transaction Files
• Dated Entries
• New – Unposted
• Current - Posted
Examples
• New.GLT
• Current.GLT
• History.GLT
System/Standard
Files
• Setup Records
• Rates
Examples
• System.PRS
• Standard.BLS
Others
• Control File
• Issues
Examples
• TS.CTL
• Issue.IAI
• Issue.APZ
Files & File Names – Data Flow (General Ledger)
Data Entry
New.GLT
Transactions
Post Entries
Current.GLT
Transactions
Update totals
Master.GLM
Accumulated
Totals
Files & File Names – Data Flow (Job Cost)
Data Entry
New.JCT
Transactions
Post Entries
Current.JCT
Transactions
Update totals
Master.JCM
Accumulated
Totals
Files & File Names – Data Flow (Accounts Receivable)
Data Entry
New.ART
Transactions
Post Entries
Current.ART
Transactions
Activity.ARA
Update totals
Master.ARM
Accumulated
Totals
Files & File Names – Data Flow (Accounts Payable)
Data Entry
New.API
Invoices
Post Entries
Current.APT
Transactions
Update totals
AP Master
Accumulated
Totals
+ Invoices
Files & File Names – Data Flow (Between Applications)
General
Ledger
Accounts
Payable
Accounts
Receivable
Job Cost
Exercise #2 – Observing Data Flow
1. Enter a simple set of journal entries.
2. Capture the current state of the accounts.
3. Create some ad-hoc queries using Office
Connector.
4. Post Entries.
5. Observe what changed.
Names, Other Names, & More Names
Custom
Descriptions
• Change to reflect customizations
• Intuitive
• Used by Report Designer & Default ODBC DSNs
Standard
Descriptions
• Never change
• Intuitive
• Used with Crystal Reports & ODBC Solutions
Dictionary
Names
• Never change
• Less intuitive
• Used with Office Connector & ODBC Solutions
Names, Other Names, & More Names – Tables
Custom Description
Standard Descriptions
Dictionary Names
MASTER_JCM_COST_CODE
JCM_MASTER__COST_CODE
MASTER_JCM_RECORD_3
MASTER_JCM_PHASE
JCM_MASTER__EXTRA
MASTER_JCM_RECORD_2
MASTER_APM_VENDOR
APM_MASTER__VENDOR
MASTER_APM_RECORD_9
MASTER_APM_INVOICE
APM_MASTER__INVOICE
MASTER_APM_RECORD_1
CURRENT_GLT_BATCH
GLT_CURRENT__BATCH
CURRENT_GTL_RECORD_1
NEW_JCT_TRANSACTION
JCT_NEW__TRANSACTION
NEW_GLT_RECORD_2
Custom Description
Convention
Standard Description
Convention
Dictionary Name
Convention
FILENAME_TYPE_CUSTOMCAPTION
TYPE_FILENAME__STANDARDCAPTION
FILENAME_TYPE_RECORD_NUMBER
(note – two underscores after FILENAME)
Names, Other Names, & More Names – Columns
Custom
Descriptions
Standard
Descriptions
•
•
•
•
•
•
•
•
Account
Phase
Cost_Code
Amount
Account
Extra
Cost_Code
Amount
Dictionary
Names
•
•
•
•
TACCT
CEXTRA
ODJCPHS
TAMT
Data Security
Securable Items:
• Menu/Screen Access
• ODBC Read/Write
Access
• Specific Records
• Reports
Your Feedback is Important to Us!
•
Completing a session survey is fast and easy:
Stop by a Sage Summit Survey kiosk or complete the
survey on your mobile phone, laptop, or tablet through
Sage Summit mobile app.
the
– IOS, Blackberry, or Android users may download the app
from the App Store by searching “Sage Summit”
– Laptop users may use this link www.sagesummit.com/webmobile
•
Remember each completed survey is another entry for one of several
daily prize drawings, including an Apple iPad!
•
Your feedback helps us improve future sessions and presentation
techniques.
•
Session code for this session: C-0634
Contact Information
• Presenter Contact Information:
– James Coyle: [email protected]
– Michael Newland: [email protected]
• Follow us on Twitter: @Sage_Summit
– Use the official Sage Summit hashtag: #SageSummit
• Don’t forget to use the Sage Summit mobile or web app for all your
conference needs.
• Access presentations on Sage Summit Virtual Tote Bag.
– www.sagesummit.com/virtualtotebag
Thank you for your participation.