Need the table relationships

[antoncrowley]

Can someone post a pdf or picture of the table relationships for mas 90, or provide a link of where to download.


thanx in advance

 

[BigLouie]

Everything you need to know is on the Technical Reference and Support Guide CD. Can I ask what you are trying to do.

 

[BigLouie]

Based on your request I am guessing that you are a programmer brought in to either write reports or get data in or out of MAS. Let me point out a few things.

The ODBC driver is READ ONLY. To get data in you must use VI. No work around.

There are four basic types of files in MAS.

Master File
Data Entry files
Historical files
Summary/perodic files

The invoice files are kept as header and detail.

 

[antoncrowley]

thank you for your replies guys.

first off, i never even heard of mas 90 before my new project so please forgive my ignorance.

yes i am looking to write some reports for a local company. i have read up about the read only driver that ships with mas 90, it's fine since i only want to dump the data and report off of it. i was thinking i was just going to dump the data into a sql server 2k5 database, can you guys see any problems with this or have you had any experience connecting to mas 90 from the new management studio? i was thinking it should be the same as other data imports but a lot of people seem to have some big problems with the connection through the providex driver.

i hope the company kept the technical reference cd. if for some reason they lost it, do you know of anywhere else to get the diagrams i am looking for.

thanks again.

 

[BigLouie]

The reseller should be able to get you a copy. I use Access to get the data. If you are going to get a lot of tables then just do a simple import table. If you are going to refresh the data on an on-going basis then you would need to setup a silent DSN. That is another DSN using the SOTA90MAS driver and hard code the log in and company information and then pull from there. When pulling you have to pull in by table. You cannot use joins.

 

[BigLouie]

If you can detail what you are going to report on I should be able to tell you what tables to pull.

 

[TheOtherBinky]

Sounds like he's having the same problem I am. I also need to write reports out of MAS90 using its General Ledger data, but I'm not sure if I'm better off using FRx or Crystal or MS Access.

I've used Crystal and MS Access with other databases but I'm new to FRx (and MAS90) and I'm not really sure if FRx's worth learning unless I have to.

Anyone out there have a preference or could point me in the "right" direction ??

Thanks.

 

[BigLouie]

FRx is generally the prefered choice when reporting on the GL. It allows you to do things that Crystal and Access cannot.

 

[TheOtherBinky]

I see that FRx has a lot of built-in functions to make things easier but what can it do that Crystal or Access can't ?

 

[Blorf]

Nothing, to be sure, and probably even less than what Access or Crystal can do, but like you said, it has built in functions, and is already designed for financial reporting.

It boils down I think, to what your skills are. Myself, I do it all in access, including financial reports, because I am good with access, and not so good with FRx.

ChaZ

There Are 10 Types Of People In The world:
Those That Understand BINARY And Those That Don’t.

 

[BigLouie]

People who know FRx as well as Crystal and Access use FRx for GL reporting. It's ability to roll up accounts, report on multiple companies gives it a big plus. It is built for financial reporting off the GL. Now while Crystal and Access can do the same you have to really know the product and know how to write formulas to get it correct. With FRx that is all taken care of for you.

 

[TheOtherBinky]

Right now I'm using both Access and FRx. Access didn't care at all for the ODBC (v3.33) that came with the CD, but likes the V4.0 that's available on the Sage/ProvideX Web site.

It looks like I'll use FRx for now and keep messin' and looking around using Access so I can later hopefully break away from FRx and get use to things in an Access/Crystal way.

I do have a question about Access and MAS 90. FRx wouldn't work until an option on MAS 90 was set, is anything needed for Access or Crystal? I've linked to all the MAS90 GL tables via Access and some tables show their data just fine (GL_Account) but others show nothing (GL_TransactionJournalDetail). I guess it's how the accounts are being used and nothing to do with Access ?

(And thanks everyone for your replies and help)

 

[BigLouie]

Some show data and others show nothing because some only hold data during transaction entry. In this case the GL_TransactionJournalDetail only holds date during data entry. This is why I always suggest that anyone writing reports take the Data Files class.

And I would suggest using SQL Specific Pass Through Queries in Access rather than linked tables because you can code in the log in and they return data twice as fast and with less error messages.

 

[TheOtherBinky]

This is why I always suggest that anyone writing reports take the Data Files class".
-- Thanks. I checked and I'm looking into that right now.

"And I would suggest using SQL Specific Pass Through Queries in Access".
-- Thanks again. I would rather use SQL Server and Stored Prcoedures + Crystal Reports but using Access is the better way to go for me right now (most all of our other reports use MS Access).

I'm getting a lot of good info here at this Web site.

 

[TheOtherBinky]

I must be missing something.

When I write reports in FRx -- and I've done a total of 3 now -- it happily lists our MAS90 GL accounts and their current amounts and even YTD amounts if needed. But when I use MS Access I basically get very little, even though I can see all the MAS90 tables when linked to Access. I can get info on the GL accounts themselves but nothing on the amounts stored in them. No luck either by linking the tables or via simple pass-through SQL Selects.

I'd like to use Access as a way to verify what FRx is giving me. I checked all the GL tables shown by Access and I'm not seeing much data. Any ideas ?

Thanks.

 

[Blorf]

Hi.

In Access, what tables are you looking at? If you are not a database kind of person, going to Access may not work for you, as most of the data is to some degree (for example, retained earnings, YTD amounts...) are calculated fields, and not stored anywhere.

Take a look at GL8, record type A for actual, and look at your Fiscal Year field to pull data by account code.

Hope that helps,
ChaZ

There Are 10 Types Of People In The world:
Those That Understand BINARY And Those That Don’t.

 

[TheOtherBinky]

Thanks Blorf.

GL8? is that "G/L Budget & History File" in v3.61 of MAS90?
We're using v4.05 and according to the file layout that may be "GL_BudgetAndHistoryWrk" for us.
Either way I haven't had much luck with any of the Budget tables listed.

 

[BigLouie]

Perfect example of why FRx is better. Also the need for Data files class. Any file that ends in Wrk is a works file and is ONLY populated when a report is run. You can find the table layouts in the TRSG. Please note that in v4.XX the GL changed completely. You might be able to find out the tables by reading the TRSG and by trial and error. Or you could just use FRx.