1 / 35

GlobalWare via ODBC

GlobalWare via ODBC. Agendum. DBISQLC – the other white meat What, why, how Some things you may not know about using ODBC and/or Access Linking tables Null vs empty/blank Invoices with comments and without comments Prompts Parsing fields Report by month, by day of week

lassie
Download Presentation

GlobalWare via ODBC

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. GlobalWare via ODBC

  2. Agendum • DBISQLC – the other white meat • What, why, how • Some things you may not know about using ODBC and/or Access • Linking tables • Null vs empty/blank • Invoices with comments and without comments • Prompts • Parsing fields • Report by month, by day of week • Segments with invoice data (e.g., commission) • Agent name

  3. DBISQLC • Aka ISQL (“eye see kwul”) • What is it? • Interactive Structured Query Language • Native commands – direct access to gblware.db • Installed with GlobalWare client • Why? • Some things you can’t do in tools like Access • Sometimes easier • Can see nulls • You can use the F9 key

  4. DBISQLC • How? • Start, Run, DBISQLC • Login as GLOBAL or EDIT and password. Select Gblware32 for Data source name.

  5. DBISQLC • List the Account Ids associated with a Report To Id • In Access, you’ll get a line for each Account Id • In DBISQLC, you can get one line for each Report To Id select ReportToId, list(AccountId) as Accts from dba.customer group by ReportToId order by ReportToId

  6. DBISQLC • Results display, but you can export to file select ReportToId, list(AccountId) as Accts from dba.customer group by ReportToId order by ReportToId; Output to c:\rptid.txt

  7. Microsoft Access • Linking tables • Important to select correct fields for primary key • Comments, InvPayId and LineNum • Primary key for each table listed in Print DB Structure • If you get ODBC call failed, try relinking the table

  8. Null vs Empty • Empty/blank: initialized but containing a zero-length value. • Null: uninitialized or containing no value, not even a zero-length value. • GlobalWare is not always consistent • You can see nulls in ISQL but not in Access

  9. Null vs Empty • ISQL select doctype, traveler, * from dba.invoice where invoicedate = '2007-08-31' and traveltype = 'a' and (doctype <> 'exc' or doctype is null)

  10. Null vs Empty • Microsoft Access

  11. Microsoft Access • Some things you may not know about using Access • Prompts

  12. Prompts - Microsoft Access • Prompt for variable data, like date range • Enter in square brackets [ … ] • Text within displays to user • For non-text fields, set up parameters • Validates data at entry

  13. Prompts - Microsoft Access

  14. Microsoft Access • Some things you may not know about using Access • Invoices with comments and without comments

  15. Invoices with comments and without comments • Access – two queries • Query of the comment lines for the specific number • Query of Invoice table and the 1st query • ISQL – query with subquery • Query of Invoice table • Subquery of the comment lines for the specific number

  16. With/without comments - Microsoft Access

  17. With/without comments – Microsoft Access

  18. With/without comments – ISQL select number(*), (select data from dba.comments where invpayid = payid and linenum = 92) as Data92, * from dba.invoice where invoicedate between '2007-08-31' and '2007-08-31‘ and status <> 'v' order by data92

  19. With/without comments – Microsoft Access • Easy to go from here to find missing comments • Data is null

  20. With/without comments - ISQL select (select data from dba.comments where invpayid = payid and linenum = 92) as Data92, * from dba.invoice where invoicedate = '2007-08-31' and data92 is null But easier: select * from dba.invoice where invoicedate = '2007-08-31' and payid NOT in (select invpayid from dba.comments where linenum = 92)

  21. Microsoft Access • Some things you may not know about using Access • Parsing fields

  22. Parsing Fields - Microsoft Access • Get part of a field, e.g. • original ticket number on an exchange is in Savings comment ‘EX – 1234567890’ • Last 4 digits of credit card number • LEFT, RIGHT, MID

  23. Parsing Fields - Microsoft Access

  24. Parsing Fields - Microsoft Access • More examples Department: Left(Sort1,1) DeptNum: Mid(Sort1, 3, 4) CreditCard: ‘********’ & Right(CCNumber, 4)

  25. Parsing Fields - Microsoft Access • Special functions for parsing dates • Allows, for example • Month by month sales totals • How much business do I do on the weekend?

  26. Parsing Fields - Microsoft Access • Get the month from a date: • InvMonth: Month([invoicedate]) 1 - 12 • InvMonth: Format([invoicedate],'mmm') Jan - Dec • InvMonth: Format([invoicedate],'mmmm') January - December • You need one to sort on and one to print • If spanning years, need year and month for sorting

  27. Parsing Fields - Microsoft Access • Get the weekday from a date: • InvDay: weekday([invoicedate]) 1 - 7 • InvMonth: Format([invoicedate],‘ddd') Sun - Sat • InvMonth: Format([invoicedate],‘dddd') Sunday - Saturday • You need one to sort on and one to print

  28. Microsoft Access • Some things you may not know about using Access • Segments with invoice data (e.g., commission)

  29. Segments with Invoice data - Microsoft Access • Data from Invoice repeats on each segment row • Which means, amount fields will be multiplied by the number of segments in totals and subtotals

  30. Segments with Invoice data - Microsoft Access • Quick and dirty, get amounts on 1st segment only Comm: Iif (SegmentNum = 1, CommAmount, 0) Doesn’t work if there is no segment #1 (can happen on partial refunds) • Apportion to all segments (divide by the number of segments) • Query the number of segments in each Payid

  31. Segments with Invoice data - Microsoft Access • Apportion to all segments (divide by the number of segments) • Query the number of segments in each Payid • Include that query in the main query and divide Comm: CommAmount / NumSeg

  32. Microsoft Access • Some things you may not know about using Access • Agent Name

  33. Agent Name - Microsoft Access • If agents’ sine <> account Id (which sometimes has to be):

  34. EDIT user • Original set of fields fairly limited • Insert and Delete rights to Comments added in 3.40 • Insert, update, delete rights to HccRecData for a modest fee • Other fields and tables possible for fee – let’s discuss – such as • TempCashRcpt • Get Cash Receipts reads this, so this could provide a CR import • Credit card data (some of you have this already) • Account Id date open (bug has been fixed but historical could be wrong) • Others? (can’t be true accounting; “in sync” fields, maybe)

  35. Access – What else? • I know we didn’t cover all of these topics in PHX • If you have questions, please feel free to email me – my email address is in the attendee list

More Related