Search Project Mgmt
Search FileMaker Blogs

Thank you for visiting the FileMaker Thoughts blog. I recently moved this content over from my blogger account. Hope you like it! When you get a chance, check out the centralized search feature for all the FileMaker blogs found along the right side panel. It is quite handy!


Thursday
Feb242011

FileMaker And The ERD

From Dwayne Wright PMP
Certified FileMaker Developer

WEB: www.dwaynewright.com
EMAIL: info@dwaynewright.com
TWITTER: dwaynewright
YOUTUBE: FileMakerThoughts

Please Note: If you are viewing this page in a news feeder, the images may get munged up a bit. For the best experience, please visit the journal directly by clicking (here). 

I had a customer requiring me to document software requirement specifications for each phase of our project. I suggested doing an ERD as well and they liked this idea. So I thought I'd share what I came up with in a blog post.

WHAT IS AN ERD?
The Entity Relationship Diagram (ERD), is a project management tool for database projects and can be similar to a rough blueprint for building a house. The primary goals of a successful ERD is to ...

- identify primary database entities
- identify the attributes of each entity (to some degree)
- identify the relationships between entities using links between attributes

There isn’t a single ERD type and there are only a few hardened rules to follow. Many believe that it is best to adhere as closely as possible to industry standards and fully document where you may stray from the established formats.

Some have commented about the similarities between an ERD and the FileMaker Relationship Graph. There are some stark differences between the two but one can  help influence the other. Ideally, if you are going to create an ERD it should be in place before Relationship Graph takes shape.  

The thing about the Relationship Graph that greatly strays from the ERD mentality is that each entity can (often must) appear multiple times within the relationship graph. This is absolutely not the case with an ERD and makes it a bit challenging to blend the two for the non full time FileMaker developer.

OUR ERD
You will see an entity called Payment Adjustments. The deal is that insurance companies may often miss paying a claim by a few dollars. It isn’t worth their time to try to get this money from the customer or the insurance company, so they are writing it off. You may think this should be an attribute of applied payments entity and not an entity in itself. In this case, the sheer number of records, the security settings around who can give a payment adjustment and the reporting needs led me to the decision to propose it as a separate entity.

You may need to click to expand

Here are my thoughts ...

CLIENTS: Related to zero or more invoice records.

** It is possible a new client has gotten credit approval but hasn’t placed an order yet.

INVOICES: Optionally related to one client.
Mandatory Related to at least one (probably more) invoice line items

** This is a bit of a stretch because I have seen invoices that have no line items and a shipping charge. However most invoices print from the line items table. So if this isn’t a mandatory relationship, it might not be printable. Also, it may be a business rule that all invoices be linked to a client record.

INVOICE LINE ITEMS: Mandatory relationship to one invoice
Optional relationship to one inventory product

** This is a bit of a stretch but I’ve seen instances where customers want to enter in something in a line item that is not an inventory product.

PAYMENTS: Optional one to many relationship to applied payments

** This is a bit of a stretch but it is possible that deposits or future gift cards might exist that have not been applied to a payment yet

APPLIED PAYMENTS: (join table) Mandatory relationship to a single invoice and a single payment record

ADJUSTED PAYMENTS: Mandatory relationship to a single invoice

** This could be a type of applied payment without an associated payment but I recommend it be in a separate table for reporting and security needs

INVENTORY: Related to zero or more invoice line item records.
Related to zero or more purchase line item records.
Related to zero or more inventory adjustment records.

** It is possible an inventory product hasn’t been sold or ordered yet.

INVENTORY ADJUSTMENTS: Mandatory related to one or more inventory records.

VENDORS: Related to zero or more purchase order records.

** It is possible a new vendor has not been used on a purchase order yet.

PURCHASE ORDERS: Optionally related to one vendor.
Mandatory related to at least one (probably more) purchase lines

** Although I think it is best practice that a purchase order be linked to a vendor record, I have seen customers requesting otherwise.

PURCHASE LINE ITEMS: Mandatory relationship to one purchase record
Optional relationship to one inventory product

** This is a bit of a stretch but I’ve seen instances where customers want to enter in something in a line item that is not an inventory product.

=
More info about the author and FileMaker in general, contact me at info@dwaynewright.com.

© 2008 - Dwayne Wright - dwaynewright.com

The material on this document is offered AS IS. There is NO REPRESENTATION OR WARRANTY, expressed or implied, nor does any other contributor to this document. WARRANTIES OF MERCHANT ABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE EXPRESSLY DISCLAIMED. Consequential and incidental damages are expressly excluded. FileMaker Pro is the registered trademark of FileMaker Inc.

Tuesday
Feb222011

A READER ASKS: FileMaker 11 Book Recommendations

From Dwayne Wright PMP
Certified FileMaker Developer

WEB: www.dwaynewright.com
EMAIL: info@dwaynewright.com
TWITTER: dwaynewright
YOUTUBE: FileMakerThoughts

Please Note: If you are viewing this page in a news feeder, the images may get munged up a bit. For the best experience, please visit the journal directly by clicking (here). 

A READER ASKS
I'm looking for some good books for FMPro 11. Any recommendations? I have 30 years experience with database design and application development on IBM mid-range systems,  and am looking for information on how FileMaker works.
-------
DWAYNE RESPONDS
I'd have to say that I cannot recommend any FileMaker 11 books because I haven't picked any up.

The last FileMaker book I looked at was The FileMaker 10 Bible by Ray Cologon. In fact, I don't think they came out with a FileMaker 11 version. There is also the FileMaker Missing Manual series that was quite good but the FMP version 9 is the last one I saw of it. The FileMaker Training Series is also an option and quite good in its own way. It doesn't follow a classic book format but more of a robust classroom companion reference. Once again, the FileMaker 10 update is the last one I viewed.

Most FileMaker 10 books are still a fine option because the FileMaker 11 update is not that challenging. Don't get me wrong, I really like FileMaker 11 but charting is the only change that has any level of significant learning curve. Speaking candidly, I cannot seem to find any client that cares about adding charting to their database workflow, so I wouldn't recommend investing that much time learning it.

If I were to recommend something, it wouldn't be a book. If you can find a book at a price you are comfortable with, by all means go for it. The FileMaker 10 Bible is about $27.00 and the Missing Manual is about the same price. The FTS books are a bit overpriced at $100 but you can get it at half price if you join TechNet.



Oh, I did mention something about a recommendation didn't I? Well, I would certainly recommend FileMaker's TechNet! It wouldn't recommend it for beginners but certainly for any mid to advanced level developer. For the beginners, I would recommend the FileMaker training series videos from VTC.com or Lynda.com. A monthly membership, an all you can eat technical buffet, is quite reasonable. John Mark Osborne (vtc) and Cris Ippolite (lynda) take different paths in presenting the content but both are quite excellent

Thursday
Feb172011

Page Up / Page Down FileMaker Portal Rows

From Dwayne Wright PMP
Certified FileMaker Developer

WEB: www.dwaynewright.com
EMAIL: info@dwaynewright.com
TWITTER: dwaynewright
YOUTUBE: FileMakerThoughts

Please Note: If you are viewing this page in a news feeder, the images may get munged up a bit. For the best experience, please visit the journal directly by clicking (here). 

This example is a modified version of our "Go To Particular Portal Row" example. Here we have added a small modification to the Go To Row Number button and have added the ability to page up and down a portal row.


The modification to the Go To Row Number is that we removed the value list. Now the user can type into a field and enter in any valid number and go to the portal row.

The Page Up / Page down feature is a little more complex but not that bad really. In our example, we added 3 fields ... a global number and two calculation fields with a number result. The calculation field count a value of the next portal row based upon the captured portal row and the number of rows shown.

The way it works is a script sets the Current Portal Row Global field equal to the current portal row using the Status(CurrentPortalRow) function. Then we use the following calculations to decide which portal row we want to go to with the script steps of Go To Portal Row [ By field ] using the page up calculation in the page up script and the page down calculation in the page down script. The calculations are as follows...

PAGE UP CALCULATION
Case(
(Current Portal Row Global - 9) < 1, 1,
Current Portal Row Global - 9)

which simply says give us a portal row number 9 less than the current one. If that total is less than 1 ( which would be the case for rows 1 - 8 ), then go return the portal row number 1.

PAGE DOWN CALCULATION
Case(
(Current Portal Row Global + 9) > Count(self_universal::Universal_Link), Count(self_universal::Universal_Link),
Current Portal Row Global + 9)

which simply says give us a portal row number 9 more than the current one. If that total is greater than the total number of rows ( we use the aggregate count function to determine this ), then go return the number of the last number in the portal.

An example file can be downloaded by clicking (here)

FYI ... UNFAMILIAR WITH SELF RELATIONSHIPS?
A Self Relationship is a common term for a situation in which both sides of the FileMaker relationship are the same table. It is even possible that the matching key fields are the same field. This can be handy when using the Go To Related script step or when using Aggregate Functions.

Self relationships can be one to many, many to many or even the filtered varieties.

Here you can see a self relationship I use in the FileMaker database I use to contain all my blog related data. In this case, I have my blog related to itself using the same field of blog. This way I can be writing a blog for my Security blog and quickly see all the previous security related blog posts in a portal by its side.

More info about the author and FileMaker in general, contact me at info@dwaynewright.com.

© 2008 - Dwayne Wright - dwaynewright.com

The material on this document is offered AS IS. There is NO REPRESENTATION OR WARRANTY, expressed or implied, nor does any other contributor to this document. WARRANTIES OF MERCHANT ABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE EXPRESSLY DISCLAIMED. Consequential and incidental damages are expressly excluded. FileMaker Pro is the registered trademark of FileMaker Inc.

Thursday
Feb172011

Navigate FileMaker Portal Rows Via Crafted Buttons

From Dwayne Wright PMP
Certified FileMaker Developer

WEB: www.dwaynewright.com
EMAIL: info@dwaynewright.com
TWITTER: dwaynewright
YOUTUBE: FileMakerThoughts

Please Note: If you are viewing this page in a news feeder, the images may get munged up a bit. For the best experience, please visit the journal directly by clicking (here). 

In this example, we show a very easy technique of moving from one portal row to the next via buttons. On the surface this seems to be a very trivial example but it does show that you can control FileMaker's ability to navigate portal rows. This can become very helpful as you start to build larger and more complex scripts and interfaces.

FIRST, PREVIOUS, NEXT and LAST
On our layout, we have 5 buttons which are First, Previous, Next and Last. These will allow users to navigate portal rows and can come in handy if your audience is new to FileMaker. For example purposes, we show a portal listing the seasons of the year which are Spring, Summer, Fall, Winter and Football. The last is my favorite, I must say. These four buttons are not even attached to a script but are wired to button action choices. You could certainly script this action and you might want to if you wanted to run some other operation piggy backed to the portal row navigation action.

PORTAL ROW VIA AN ENTERED NUMBER
We also added a button to go to a portal row by an entered number. In this case, we did attach this button to a script  and all you need to do is choose a portal row number from the radion button value list above the button and then click the button.

This is a somewhat lame example of the "Go To Portal Row By Number" feature. Generally this is used in more sophisticated batching operations that may involve many records. The ability to go to a portal row via a calculated value opens up plenty of options for the creative programmer that stumbles upon unique needs.

A MODEST TWEAK TO THE SCRIPT
I only added one tweak that is a little off of the beaten path. If the Portal Number field is empty, the script brings up a dialog box asking what portal row you want to go to . The script looks like this ...

If [

IsEmpty(Portal_Number)]
Go To Portal Row [ Select By Number ]

Else
Go To Portal Row [ Select, "Portal Number"]
** which is the name of the field **
End If

An example file can be downloaded by clicking (here)
=
More info about the author and FileMaker in general, contact me at info@dwaynewright.com.

© 2008 - Dwayne Wright - dwaynewright.com

The material on this document is offered AS IS. There is NO REPRESENTATION OR WARRANTY, expressed or implied, nor does any other contributor to this document. WARRANTIES OF MERCHANT ABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE EXPRESSLY DISCLAIMED. Consequential and incidental damages are expressly excluded. FileMaker Pro is the registered trademark of FileMaker Inc.

Sunday
Feb062011

Reporting In A New FileMaker Window

From Dwayne Wright PMP
Certified FileMaker Developer

WEB: www.dwaynewright.com
EMAIL: info@dwaynewright.com
TWITTER: dwaynewright
YOUTUBE: FileMakerThoughts

Please Note: If you are viewing this page in a news feeder, the images may get munged up a bit. For the best experience, please visit the journal directly by clicking (here). 

This is a fairly simple report that comes up in the new window. We only have two data fields which include an invoice date and an invoice amount. What we want to do is show a report that summarizes the invoice sales for each year and then further broken down by the month of the year. The report will run on the found set of records. So you can do a find for a particular set of records and then run the report.


There are two sorted by subsummary layout parts the sort fields uses are both calculation fields. The first is year of the sale and the second is the month of the sale. There is a script that runs the report in a new window and a subscript that handles the report printout actions.

Clicking the Run Report button will activate the report. You might want to have the script debugger running if you have FileMaker Advanced installed on your machine. That will allow you to see the individual script steps execute one at a time from the script debugger window.

An example file can be downloaded by clicking (here)
=
More info about the author and FileMaker in general, contact me at info@dwaynewright.com.

© 2008 - Dwayne Wright - dwaynewright.com

The material on this document is offered AS IS. There is NO REPRESENTATION OR WARRANTY, expressed or implied, nor does any other contributor to this document. WARRANTIES OF MERCHANT ABILITY AND FITNESS FOR A PARTICULAR PURPOSE ARE EXPRESSLY DISCLAIMED. Consequential and incidental damages are expressly excluded. FileMaker Pro is the registered trademark of FileMaker Inc.