# Any input on relational databases?



## On The Rock (Feb 5, 2010)

One thing that irks me on CT is when new members start a thread to ask a question without doing a search first. Most of the time, their question has been dealt with comprehensively in past threads, and they could get answers much faster if they weren't so lazy. They would save other members a lot of time, too. So....

....Forgive me if this has been discussed already -- I tried searching, and I almost can't believe this hasn't been discussed yet, but....

....I can see a ton of wasted time building, maintaining and analyzing data from 2 or 3 separate file sources like Excel, Quickbooks, etc. I am seriously considering using Filemaker Pro 11 to save a bunch of time in the long run and to have a more powerful tool for reports and marketing.

Does anyone have any input regarding or experience with relational databases in their business, especially to track data for marketing purposes? If so, does it serve your business well when it comes to invoicing? I know that most relational databases are not good for accounting, but I think there's a product available for linking FMP 11 and QB....

Thanks.


----------



## JustaFramer (Jan 21, 2005)

I just looked at Filemaker Pro. Looks like a front end GUI of a SQL flavor. I have a LAMP stack on my laptop for testing PHP/MYSQL. It takes time and money to set up a DB to suit if you want custom. Of course you could do a WAMP stack too. 
So basicly that is just a lite server package for out of the box simplicity and you won't need a full time IT guy to keep it running.


----------



## Vince_B (May 9, 2008)

I use MS Access mostly for marketing purposes. It has helped immensely in spotting trends that would have otherwise gone unnoticed. 

Most of the work in setting it up is in the planning, deciding what data you want to include. Once you have done that the rest is pretty easy.


----------



## jmiller (May 14, 2010)

What did you search for? Try: 
CRM site:contractortalk.com [pasted into google].

in your defense, relational site:ct.com didn't turn up much, and relational database site:ct.com was 0 hits.

Not that I mind the topic coming up again. I'm trying to research the same thing right now. I'll have to google some of JustaFramer's ideas, as i'd like the front end of ours to be web accessible with the sql on our web server. Easier said than done probably.


----------



## JustaFramer (Jan 21, 2005)

That is where the PHP with some HTML comes it to play. You could do it all in PHP but the html will give it some pazazz. I have created a generic to do list in just php that utilizes my mysql database.


----------



## jmiller (May 14, 2010)

How do estimates and photos get stored in a db?


----------



## JustaFramer (Jan 21, 2005)

jmiller said:


> How do estimates and photos get stored in a db?



PHP for the photos no need to db them with sql. I have a very rough script I created that would work for that. The estimate part would require a front end set up in php again and linked to db. 

Don't have anything set up for that. I would have to ask my father in law for help with that. He knows his **** in database admin.


----------



## Jerrald Hayes (Apr 24, 2006)

Hello On The Rock

I was just over on RC where I posted about the 2 for 1 end of the year special that FileMaker Inc. is running right now and bconley told me:



> Jerald, FYI, there is a new thread on CT that's right up your alley


I'll repost what I wrote there first about the FMP Special then comment more directly on your questions here.

———————————————————————————————

While talking to one of my contractor buddies today about databases vs. spreadsheets like Excel I suddenly recalled that FileMaker Inc. has a special 2 for 1 deal going on for the next three weeks until December 22nd 2010. Here is a link to *the web page (email) that was net to me about the deal*.

If you're interested in exploring it you can get a thirty day demo of FileMaker Pro 11 for Mac or Windows to test it out *here*.

For anyone who wants to get into using databases and needs multiple licenses for a more than one person operation I think this is just a great deal. Even if your not into creating your own databases to track items, tasks etc .the FileMaker Pro application comes with 30 built-in *Starter Solutions* like Invoices, Contact management, Task Management, Issue Tracking, etc. that are incredibly useful (and there are some pretty kool free and for fee third party solutions out there too) and with *FileMaker Go* you can put any FileMaker solution on an iPad (or iPod & iPhone) too.

While I'm a pretty seasoned Filemaker developer (I've been working with the program since at least 1995 to the best of my recollection) I've seen some rank beginners come up with some pretty good looking simple but solid solutions on their own. I mention this because an old dog can still learn some new tricks and any new user can test out this technique playing around with the FileMaker demo too.

I was working on the To Do List that part of my own suite of programs thinking I wanted to add a container field to the individual To Do List records that I could add attachments too. You know, something like I want to attach a Excel list of contacts someone gave me to a To Do List reminder to "_Enter new contact list for project x_." 

So I get that done and as I'm testing it out I accidentally double click on that Attachment container field and this little "record" window pops up on the top left hand corner of my screen (*screenshot*) and I suddenly realize FileMaker will record a voice message for me and place it in that container field. 

I swear I don't know books I have on FileMaker and how many training videos and papers I read but I never knew I could do that.

As I mentioned FileMaker comes with it own package of 30 stater solutions and one of them is a *To Do List* tool. It took all of minute for me to add the Attachment container field and *here's what it look like*. I wrote a To Do List note to "Read the Action Tracker Manual" and inserted the Action Tracker Manual PDF into the Attachment container field.

I know Joe Stoddard over on JLC once said:



> If I was using a Mac as my main computer, and especially if you're comfortable rolling your own apps - I'd be all over FileMaker Pro. It's simply the best rapid development tool on the planet (IMHO) . It's fantastic. Not cheap but worth it if you need it. http://www.filemaker.com/


… and yes if you are on a Mac and want to develop you own solution and tools I think you be crazy to ignore FileMaker but I also think it a great tool for anyone running Windows too.

———————————————————————————————

Now as for your questions:



On The Rock said:


> Does anyone have any input regarding or experience with relational databases in their business, especially to track data for marketing purposes? If so, does it serve your business well when it comes to invoicing? I know that most relational databases are not good for accounting, but I think there's a product available for linking FMP 11 and QB....


As I mentioned above I've been working with FileMaker since the mid 90s and while it had some relational attributes back then it didn't really take off as a fully relational product until the release of version 7 in 2004 and since I stated with it way back when we've done all our invoicing in it.



On The Rock said:


> ...I know that most relational databases are not good for accounting, but I think there's a product available for linking FMP 11 and QB....


I don't know where you got that from but any as every good accounting product today is based on some kind of relational database technology. It has to be. 

And while yes there is a product that will create a real-time live link between QuickBooks and any FileMaker files you set it up to work with (*FMBooks Connector*) there are also a couple of stand alone full blown Accounting products written in FileMaker too: *Genesis Transactions Accounting & Business Metrics* from New Millennium and *Absolute Accounting* from Absolute Advantage (and we're working on our own accounting solution too).

There are also several FileMaker invoicing solutions out there for both fee and for free. For fee you have:

*SeedCode: FileMaker Invoicing*

*FileMaker Accounting and FilerMaker Invoice Template - Core2 Invoices* (Productive Computing again)

*Database Pros:Solutions:Invoicing Solution*

*Invoice 101* 

…and there are more I am sure.

And as for the free ones one of them is the starter solution that comes with any and every license of FileMaker Pro (*screenshot*) 

Any more specific question regarding FileMaker I'll be very happy to answer for you here. I'll subscribe to this thread so I'll get notified.


----------



## Jerrald Hayes (Apr 24, 2006)

[/QUOTE]


JustaFramer said:


> I just looked at Filemaker Pro. Looks like a front end GUI of a SQL flavor. I have a LAMP stack on my laptop for testing PHP/MYSQL. It takes time and money to set up a DB to suit if you want custom. Of course you could do a WAMP stack too.
> So basicly that is just a lite server package for out of the box simplicity and you won't need a full time IT guy to keep it running.


JustaFramer it seems to me you're way over-complicating working with FileMaker. With the advent of FMP 9 I think it was you could (if you wanted to) use FileMaker Pro to create front ends to external SQL databases but FileMaker has its own internal database architecture that essentially the developer essentially just builds the working front end for.


----------



## Jerrald Hayes (Apr 24, 2006)

jmiller said:


> How do estimates and photos get stored in a db?


In Filemaker Pro they get stored (and displayed) in what are called Container fields.

Here is a link to one of the great training videos from lynda.com that covers Container Fields: *Using container fields with FileMaker Pro 10 from lynda.com*

You'll notice in the video that you can also store and display (view) video files right inside a FileMaker database.


----------



## On The Rock (Feb 5, 2010)

Jerrald Hayes said:


> Any more specific question regarding FileMaker I'll be very happy to answer for you here. I'll subscribe to this thread so I'll get notified.


Wow. I never expected _you_ to jump on board!

Matter o' fact, while searching CT's archives for relational databases, I found this thread from back in '08:

http://www.contractortalk.com/f16/overhead-profit-49275/

It's starts out like it's going to be a lame "how much should I charge" thread, but instead it takes a surprising turn and winds up being one of the best business threads on CT, I think. Rather than "Thanks" everybody on every single post (which might look like flattery), I rated the thread the 5 stars it deserves. Plus, I thought with your low post count that you likely wouldn't know if I thanked you anyway. So, thanks for pinpointing the problem with Stone's Total Volume Markup method. Listening to my GC friend for the last several years and then reading Stone's book, I smelled something wrong, but I couldn't tell what it was. Ahhh, the Capacity Based method makes much more sense!

Anyhow, back to the subject at hand. As a GUI, MySQL Workbench, though free, does not seem as user friendly as I'd like it to be, and even if it's what I need, I can't tell. It's been 12 years since I touched Access, which I hardly learned anyway. I've got a Mac now, and after watching a few videos and playing with the 30-day trial and the tutorial for several hours, I'm leaning toward FMP 11. (Only need one copy, though.)

For the last 8 years, I've used Quickbooks for invoicing, receiving payments and doing some reports -- light use by most standards. And the grasp of FMP 11 I'm getting so far tells me these things are possible in FMP 11 even for me.

My question is, can I get my company out of QB and into FMP 11 with or without (without preferably) FMBooks Connector?

And thanks, Jerrald, for all the links in your posts above!


----------



## JustaFramer (Jan 21, 2005)

Jerrald Hayes said:


> JustaFramer it seems to me you're way over-complicating working with FileMaker. With the advent of FMP 9 I think it was you could (if you wanted to) use FileMaker Pro to create front ends to external SQL databases but FileMaker has its own internal database architecture that essentially the developer essentially just builds the working front end for.


I wasn't trying to suggest using both together or use a AMP stack (Apache MySql PHP). I wouldn't even suggest using any of the scripts I have cranked out. :laughing: 

There is a total advantage of using a out of the box set up and would be worth it's weight in gold. 

If I was going to have a custom DB with accounting built for me I would hire my Father in law. He has DBA'd for big company's around here and was once the lead programmer on the original piece of tax software that a lot of people use every year even Tim Geithner. :whistling It would probably cost me more than having all the apps with Filemaker. But what can you do? He doesn't short change me because we are family.


----------



## Jerrald Hayes (Apr 24, 2006)

On The Rock said:


> Wow. I never expected _you_ to jump on board!
> 
> Matter o' fact, while searching CT's archives for relational databases, I found this thread from back in '08:
> 
> ...


Good morning On The Rock,

Thanks for the kind words. I've been participating in contractor forums since the early days of the net way back in the early nineties so maybe my post count low since I've slowed down my posting due to my old internet age (like dog years one year equals seven internet years). I used to be a very prolific poster.

In business topics like estimating, markup, & project management I have a Estimating & Project Management Glossary that I developed over the years that I like to link to to define the phrases and terms we are all using but that is against the forum rules here so I don't bother hanging around here much and instead spend my forum time elsewhere. I do have friends and acquaintances however that tell me when interesting topics pop up here and as I mentioned in my initial post last night I guess you can thank bconley for tipping me off about your FileMaker topic.



On The Rock said:


> …Anyhow, back to the subject at hand. As a GUI, MySQL Workbench, though free, does not seem as user friendly as I'd like it to be, and even if it's what I need, I can't tell. It's been 12 years since I touched Access, which I hardly learned anyway. I've got a Mac now, and after watching a few videos and playing with the 30-day trial and the tutorial for several hours, I'm leaning toward FMP 11. (Only need one copy, though.)


*MySQL Workbench* is a pretty kool tool. I know of a second hand anecdotal (maybe even apocryphal) story where some metal working shop in New England used MySQL Workbench to design their database and then used FileMaker Pro to design the user interface. I wonder about the authenticity of it since I'm thinking why not just use FileMaker for the whole project?

Certainly FileMaker has a well earned, well deserved reputation as a RAPID development tool.

And if you do go for FileMaker it is 2 for 1 this month so since you'll get two licenses anyway so why not make a donation of your second license to your local ASPCA or some other non-profit organization and get a tax credit for it while doing something good.



On The Rock said:


> For the last 8 years, I've used Quickbooks for invoicing, receiving payments and doing some reports -- light use by most standards. And the grasp of FMP 11 I'm getting so far tells me these things are possible in FMP 11 even for me.


Yes they certainly are. It will just take some time (and some good training *Lynda.com*) to get it done and even then you can always hire a FileMaker developer for a few hours of work to straighten out or fix up anything you cobble together .



On The Rock said:


> My question is, can I get my company out of QB and into FMP 11 with or without (without preferably) FMBooks Connector?


Yes again,..for a small operation you don't necessarily need a live QB link as you move more and more accounting tasks over time over to FMP. Move just the data you do need manually by developing some copying and pasting routines.[/QUOTE]



On The Rock said:


> And thanks, Jerrald, for all the links in your posts above!


You very welcome and here is one more for you I somehow forgot. Check out this free FileMaker download I just discovered the other week. It may be all you need for a small operation.

*FileMaker Starting Point - A Completely FREE FileMaker Template Designed for use with FileMaker Pro 11*.


----------



## Jerrald Hayes (Apr 24, 2006)

JustaFramer said:


> I wasn't trying to suggest using both together or use a AMP stack (Apache MySql PHP). I wouldn't even suggest using any of the scripts I have cranked out. :laughing:
> 
> There is a total advantage of using a out of the box set up and would be worth it's weight in gold.


While setting up a *MAMP* stack on a Mac couldn't be easier (or *WAMP* (or in your case LAMP for Linux) I think for your typical contractor learning a little Apache, a bit of MySQL, and PHP along with CSS to make the interface look halfway decent is a pretty tall order for _rolling your own_ database application compared to FileMaker.

I'll bet that at least half (if not 90% or more) of the contractors here don't have any idea what MAMP, WAMP, and LAMP are and see us as just exchanging technobabble.

What kind of stuff have you developed for yourself going that route?


----------



## jmiller (May 14, 2010)

Jerrald Hayes said:


> I'll bet that at least half (if not 90% or more) of the contractors here don't have any idea what MAMP, WAMP, and LAMP are and see us as just exchanging technobabble.


They probably don’t know php, but might be capable of setting up a content managed web site. 

Tracking customers seems less complex than a public site, and I know there are free cms applications out there, so even though FM does look like exactly what our co needs right now, I'm willing to dig into some help docs or even join a forum if we can get this done free.

Luckily we are not tied to any particular accting software yet.


----------



## On The Rock (Feb 5, 2010)

jmiller said:


> What did you search for? Try:
> CRM site:contractortalk.com [pasted into google].
> 
> in your defense, relational site:ct.com didn't turn up much, and relational database site:ct.com was 0 hits.
> ...


Man, I'm sorry, jmiller -- I missed your post!

I tried searching filemaker pro, myob, bento, relational database, and I think a couple other things. I hadn't tried "access" as I thought that would kick back a zillion pages of everything but the computer program. Out of the actual 20 pages that resulted, very few had to do with the relational database.

So, I had to look up what CRM was. From my understanding, CRM software is basically contact management software. I'm vaguely familiar with ACT!, and I worked with Goldmine when I worked with Access back in '98. Not sure if either could do invoicing back then, and that would be my question, among others, today.

And it seems like there is little overlap with CRM software and what has been talked about for the most part on this thread as a DBMS. If I get the gist from these two short links...

http://www.customerthink.com/forum/difference_database_marketing_crm
http://searchcrm.techtarget.com/answer/CRM-vs-database-marketing

...and several of the threads brought up when I did your suggested search of "CRM site:contractortalk.com [pasted into google]", then I think I need a DBMS to do the kind of reports, marketing analysis, and issue tracking I have in mind.

But, hey, I am nowhere near as computer tech savvy as a lot of people, so I still might not know _exactly_ what I'm talking about.

I at least know that I want one program to meet my specific needs, I want it to be user friendly yet customizable, and I do not need something web based.

And, yes, I had no idea what MAMP, WAMP, LAMP or SAMP was before JustaFramer pointed me in that direction. But I know how big of a mental CRAMP it is trying to figure all this stuff out. It would be worse without the help from you guys, though. Thanks.

I'll keep you posted as to how and where my company's data eventually rests. Have a good weekend...


----------



## JustaFramer (Jan 21, 2005)

Jerrald Hayes said:


> While setting up a *MAMP* stack on a Mac couldn't be easier (or *WAMP* (or in your case LAMP for Linux) I think for your typical contractor learning a little Apache, a bit of MySQL, and PHP along with CSS to make the interface look halfway decent is a pretty tall order for _rolling your own_ database application compared to FileMaker.
> 
> I'll bet that at least half (if not 90% or more) of the contractors here don't have any idea what MAMP, WAMP, and LAMP are and see us as just exchanging technobabble.
> 
> What kind of stuff have you developed for yourself going that route?


I agree that diy is a tall order. I am just a idiot savant that likes to know how things work.

Currently I have stalled on any development. My intent of learning PHP for website development is what lead me to install a LAMP stack. 
The scripts I have written are very rough and need further development . But what I have so far is image manipulation for thumbnails, a todo list.
I also have one php script for finding files that were installed or added since the last run of the script. Can be great to find malware and virus's. 

I need more time with sql db to do anything real useful with it. And my web page probably won't roll out till next year some time.


----------



## jmiller (May 14, 2010)

On The Rock said:


> Man, I'm sorry, jmiller -- I missed your post!


No problem. I'm here to learn about this stuff just like you.

So if you don't need customer relationship management software, what do you need a relational db for?

I guess I assumed FileMaker was db management software, and the db we care about would be customers. Like I said I'm a newbie too.


----------



## Jerrald Hayes (Apr 24, 2006)

JustaFramer said:


> ...I am just a idiot savant that likes to know how things work...


Yeah I've got that problem too. That's how I became a Web Designer & FileMaker Developer.

I'm still building stuff,...it's just a different kind of stuff now.


----------



## On The Rock (Feb 5, 2010)

Jerrald Hayes said:


> Check out this free FileMaker download I just discovered the other week. It may be all you need for a small operation.
> 
> *FileMaker Starting Point - A Completely FREE FileMaker Template Designed for use with FileMaker Pro 11*.


That download looks promising!

And that's a good idea about how to take advantage of the 2 for 1 sale. I'll check that out, too.

Thanks, Jerrald....


----------

