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!


Saturday
Nov202010

WAREHOUSE TWEAK: Clients By Group ID (part 2)

From Dwayne Wright - Certified FileMaker Developer
WEB: www.dwaynewright.com
EMAIL: info@dwaynewright.com
TWITTER: dwaynewright

Quick recap, I created a table warehouse for a client to show annual total comparisons for different combinations. One was the combination when a client bought a product and their year by year totals. This is something many wholesalers would be interested in having. My client mentioned that some clients are organized into groups by using a group id. Now they would like to see the totals for various groups.

NEW GROUP DATA GATHERED
I have my group id data now and thought I'd give it a quick test. I did the same search I mentioned earlier for empties. I did a find for when the field has no data and omitted them from the found set. Just like the previous search about 70% of the overall warehouse set had group id data. That is a pretty good sign!

1) I export all the records that have a group id out to the desktop as a FileMaker file.

2) I duplicate my CLIENT_PRODUCT warehouse table, rename it GROUP_PRODUCT and import my recently exported data into the new table. I use the matching field names option to make it quicker to match any fields.

3) I even go back to the layout I designed for the CLIENT_PRODUCT table, duplicate that layout, reset the associated table occurrence to the GROUP_PRODUCT table. It doesn't take that long because FileMaker seems to know that I'm working on a duplicated table. As soon as I select the correct table for each field, it automatically selects the correct field to use.

OOPS ... forgot to mention the only fields I need to import are the key data fields of group id and product id. I'm going to delete all the records and then import just the data I need. In particular, my summary fields.

4) Do a quick find to see how many duplicate group id records I have. This comes up with an amazing (but not unexpected) found set of records (approximately 95%).

5) Need to isolate the original record from the duplicates. A search in FileMaker for duplicates does not omit the original. So you definitely do not want to delete the found set of records.

I tend to use the self relationship method to isolate originals from duplicates. You setup a self relationship ... using the fields you consider make up a duplicate. In this particular instance, it is the group id field.

Here you can see the simple calculation I use for detecting originals.

I do a quick look at the data set and everything looks great. I do a find for the word 'duplicate" in my new field. The search takes quite some time to accomplish but I"m only going to do this one time.

REWIRE MY RELATIONSHIPS THAT COUNT ANNUAL FEES
Next up was the task to setup the relationships I need for my annual calculations. This is going to be a multiple predicate relationship. I'm using three predicate layers here, so the scrolling to find the correct fields for the relationships was getting to be a real drag. I went over to the data file and open the table fields in the Manage Database dialog box. I reordered the Sales Order Line Item table fields so the ones I needed would be at the top. I went back to my warehouse, to see if the new fields order I set worked. Sure enough, this made the setup process much faster.

Even with a few tweaks thrown in that I didn't mention, I've wired up the dynamic calculations within 15 minutes. Took another 5 minutes to update the script that sets the static values and executed the script. Everything worked out just great. I will need to add some logic to capture new groups, new clients, new products, new suppliers and the appropriate combination thereof.

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

© 2009 - 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.



Saturday
Nov202010

WAREHOUSE TWEAK: Clients By Group ID (part 1)

From Dwayne Wright - Certified FileMaker Developer
WEB: www.dwaynewright.com
EMAIL: info@dwaynewright.com
TWITTER: dwaynewright

In a previous post (Warehousing Data Lightly Explored), I mentioned how I used data warehousing for a client. This was the type of report that presents year to year activity comparisons for sales and purchases. I showed the results to my client and they were quite happy. I was relieved as well because their database was NOT one that I created. The database is very code dense and it is challenging to see how extensive the coding prevails on the most rudimentary of tasks. My solution added a new warehouse file to the mix and has minimal interactions with the core coding of the solution.

One of those year by year comparisons was for products a client purchased. I'm showing this information in a dedicated tab panel called Comparisons. I included four sub tabs to allow the user the ability to see the data in different ways. Two of the tabs were supplier based, one showing the totals by A-Z and one by the greatest amount purchased. I can do this easily because my grand totals are all static value. I have those same to sub-tabs for products purchased.

Here you can see the tabs I'm mentioning. My client is a wine distributor, so the tabs say Winery instead of Supplier but you get the drift.

GROUP ID TWIST TO THE MIX
Now my client had a twist in mind (don't they always?) and this had to do with a way some of their client records are grouped. In their clients table, they have a group id field. This allows them to attach multiple client records together in order to form a grouping. The main reason for this is that some client records are different locations for the same overall client. So they wanted my breakdown report to show overall grouping totals for the records that need them.

SEEING WHAT SCHEMA / DATA HAND I WAS DEALT
I went ahead to see if the previous developer was capturing the group id for each sales order line item record. Sure enough, the group ID field did reside there and I did a quick search for non-empties. The group ID did have data for about 70% of the overall records, so we are pretty good to go.

GET THE NEW WAREHOUSE TABLE IN ORDER
In my current version of their data warehouse, I have a table (with data) for every unique combination of a client / product. I use the combination of the Customer ID and the Product ID fields in a relationship to get my annual total breakdowns into calculation fields using aggregate functions for Sum and Count. I guess there may even be a future need of a Average calculation.

So the table mentioned above, has a structure and data set) I need! In fact, it has a touch too much. Like removing the sculpture from a large piece of granite, I need to duplicate this table and extract the the things I do not need.

GETTING THE GROUP ID DATA
My current warehoused data does not include the group ID data. I'm going to start things off with getting the group id data added to my customer / product warehouse table. I simply add the field to the table and have it auto enter the group id data from the sales order line item table. Then I was inspired to add some more background logic. I'll add the group ID related fields from both product and customer tables. Because I'm wondering, what if a company is assigned a group id after that company already has orders in the system. Did the previous developer take this into account?

BRIEF OFF TOPIC
I do want to make sure I check on this for the client's behalf, so I quickly cruise over to basecamphq.com for add that action item for their project. I’ve been using this service for about a month now and it is working out nicely. I pull up their project and I add a new To Do category called Development Questions. I haven't actually give the customer access to the basecamphq account I set up for them. I'm going to spring the idea of them using it as a tool to manage their database needs. This will be a great opportunity to show off how we can communicate and schedule database construction activities.

(to be continued)

 



Saturday
Nov202010

Warehousing Data Explored (lightly)

From Dwayne Wright - Certified FileMaker Developer
WEB: www.dwaynewright.com
EMAIL: info@dwaynewright.com
TWITTER: dwaynewright

There are two types of database implementations but FileMaker succeeds so well at one, the other is often ignored. I’m speaking of operational database and the more reclusive warehouse databases.

Operational databases are used day in and day out to provide up to the minute reliable interactions with information. FileMaker is one of the best databases in the world for this, particularly in small to average size workgroups. Another form of database is the analytical or warehouse database, these types are often used to store large amounts of historical and static data sets. Warehoused data, unlike operational data, is locked and doesn’t undergo editing by users in any way.


Now I recently picked up a client that switched developers and they had a reporting task for me. The previous developer setup a complex report to show year by year sales data for customers and products. The report had a rather complex and intense setup process where the customer would outline what customers to report on, what products to report on and what four possible years they would want to display on the report.

Basically, it was a tab panel based data entry assistant. You make choices on the first tab and then navigate to the next tab. Based upon your first tab selections, you get a filtered set of options on the next tab. This process continues until you reach the last tab and there you can execute the script that runs the report.

This customers comparison report would use a subsummary by customer name, then by supplier and then by the products sold by a supplier. In the body of the report, would be the product id, product name, year 1 sales totals, year 2 sales totals, year 3 sales totals, year 4 sales totals and a grand total for the four select years. The final result was a classic crosstab report that the client found very useful. They wanted a couple more of these reports that showed data in different combinations and for some otherwise ignored business units.

The developers implementation of this data display was brilliant but flawed. I’ve seen this quite a bit lately in the developer community and have written about this before. The developer used a hunt and gather routine in combination with variable arrays to build the data sets. The reports took forever to run but the coding under the hood is quite remarkable. Because the coding under the hood was also largely undocumented and it used multiple subscripts, tweaking this report for my clients needs was about as flexible as a steel girder.

Sometimes the most simple implementation is the best and sometimes asking the client an extra question or two makes all the difference in the world.

These new clients are so nice and I spent a few hours trying to merge my mind into the programming created by the previous developer. I finally reached the conclusion that I would need to call the client and break some bad news to them. Due to the implementation of the previous developer, these tweaks to the report was going to be very expensive. I thought it was a shame because this could probably be a data warehouse implementation. Then I thought, “you dummy” make one of the new reports a warehouse implementation and show it to them!

I called them up and asked them one quick question, “Do your sales for previous years ever change?”. They said no, in fact, they would like for a method to lock older sales records so they cannot be edited by mistake.

So I created a new file for my data warehouse and then needed to make a first pass at populating it with data. I imported a set of records from the sales order line item table that had every unique combination of a client and a product. I then used that data to form a set of relationships back to the sales order line item table to get aggregate sum totals of each combination. Basically, I wanted to get all the totals of each year combination from 2000 to 2009. Then I created a static field and used the Replace command to put the calculated results into a field that could be indexed.

So I would have a record that had a customer id, a product id and static totals of each years matching sales activity.

The final result was a new tab in the customer file that shows a year by year breakdown of every product they have ever purchased and it all uses non-calculated static data. I have one problem with 2009 data. So I run a script from their server that updates the static 2009 totals and the customer is happy with a one day lag on their information. A customer can do standard searches for the data they want and then perform constrain finds on the found set for just about any combination they can think of and the results are immediate.

So warehousing your report data can be a very big boon for your database projects with large sets of historical data!
=
More info about the author and FileMaker in general, contact me at info@dwaynewright.com.

© 2009 - 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.



Wednesday
Nov032010

Culture Eats Strategy For Breakfast

From Dwayne Wright PMP - Certified FileMaker Developer
WEB: www.dwaynewright.com
EMAIL: info@dwaynewright.com
TWITTER: dwaynewright

If you are viewing this article via a news reader, you might not be getting the full experience. Click this link to view the article in the format intended.

Back when I worked with SolutionMakers, my boss liked to craft our work product to go into list view as the preferred experience when a user navigated from one module to another within a designed application. I, on the other hand, preferred to go into form view and constantly had to fight my years worth of muscle memory as I coded solutions to meet our corporate style guide.

The style guide has come up again in the new day job, as we begin to look over the landscape of FileMaker based solutions within the organization. This might sound odd to anyone that doesn’t know me but I’m pretty much a “go with the flow” type of guy in the office. I have many more years of FileMaker design under my belt than my corporate cohorts but that doesn’t really matter that much in regards to style guides.

A quote from this months PMNetwork magazine has kept turning around in my noggin since I consumed it. This article focuses on project management methodology but I’m applying it into so many alternative directions since I’ve reentered the cubicle nation.

“Culture eats strategy for breakfast”

I recently became a little agitated as I plowed through a collection of posts on the FileMaker TechNet developer forum. A developer had posted a question in reference to a debate between his client and himself about the database design. I was amazed and disappointed with the number of voices that chimed in “the developer needs to set the customer straight” responses. To each his own but that type of mentality will likely cause more problems than resolutions for the in house developer.  

An open mind is a great asset, particularly when there are opportunities to blend conflicting requirements into a mutually agreeable solution.

Heading back to my opening paragraph, how can a blended implementation be achieved? You can build a preference area (either globally or on a user level) that branches the experience. This method is not much of a problem in a smaller solution with a few modules but a tedious endeavor for large solutions. A more elegant solution may be to craft the experience into the navigation buttons


I don’t know where this technique originated but I recently was re-introduced to it by one of my coworkers. I think he had picked it up from one of the Soliant Consulting FTS training classes or example files. The idea is to add the list view option into the navigation button so that they can see the form and list view options together.

A blended implementation that is simple, elegant, flexible and scalable!

Wednesday
Nov032010

The FileMaker Show Custom Dialog Box

From Dwayne Wright PMP - Certified FileMaker Developer
WEB: www.dwaynewright.com
EMAIL: info@dwaynewright.com
TWITTER: dwaynewright
YOUTUBE: FileMakerThoughts

There are times in which it is handy to show a message to your FileMaker users. This can be an informative action (hey there!) or conditionally branch the database workflow (do this, do that or do the other). There are at least three ways this can be accomplished. One is to create a new layout that looks like a dialog box and then you take the user there. Another way is to purchase a third party plug-in product that gives a developer an array of robust options. My favorite and the point of this posting is to discuss the third option. This being the Show Custom Dialog box script step. This handy method has many  features such as ...

- the ability to show a customized title and message
- the ability to add data into three possible fields
- the ability to include three buttons that branch a script accordingly.

Other  features  include  the  ability  to  use  literal  text  or  a  calculation  to  show  the  dialog  box  title,  the  dialog  box  main  message  and  the  labels  for  the  input  fields. 

An example file can be downloaded by clicking (here)

ABOUT THE TITLE AND MESSAGE AREAS
You can hard code information or use calculated values in the title and message areas. Using a calculated value, you can craft almost any kind of message for any type of situation you can imagine.

Here  you  can  see  the  General  Tab  of  the  Specify  Custom  Dialog  Box.  Notice  how  you  can  choose  to  enter  in  your  own  Dialog  Title  and  Dialog  Message  information  (  either  a  literal  text  string  or  via  a  field  in  the  database  solution  ).  You  can  also  see  how  you  have  3  tabs  for  allowing  input  fields  into  the  database. 

ABOUT THE DATA ENTRY FIELDS
The fields that you use for data entry do not have to be on the current layout and field validation you have setup for the selected fields will still take place. The same is true for any access privilege settings you might have for those fields, although those settings can be overridden using that check box option in the script itself.


If you use global fields for data entry in the Custom Dialog Box, you might want to clear them of data at the start or end of the script. Otherwise, they may contain the data the user had included in a previous execution of this script in the same session.

Here  you  can  see  the  second  tab of the Show Custom Dialog box is activated and  you  can  click  a  check  box  to  enable  the  feature  of  entering  data. These options pass data to fields in the database as the user types in content. You  can  specify  which  field  that  data  goes  to  and  even  show  the  password  dot  characters  as  the  user  enters  the  data.  You  can  also  specify  the  label  name  for  the  input  field  (  either  literal  string  or  a calculated value).

ABOUT THE BUTTONS
How about we go back and discuss those buttons we can include in our dialog box? We can add up to three buttons in the dialog box and ultimately these can be used to execute different operations. Under the hood, FileMaker has the ability to tell the script which button the user selected. Here we need to discuss the Get(LastMessageChoice) function.

ABOUT GET(LASTMESSAGECHOICE)
Using this function, you can see which of the 3 possible choices the user made from the dialog box the Show Custom Dialog script step provides.

In the dialog box, the users selection of the first choice on the right will return a 1

In the dialog box, the users selection of the middle choice will return a 2

In the dialog box, the users selection of the first choice on the left will return a 3

It is kind of weird but the order is reversed when the developer is setting up the Show Custom Dialog. In this case, the option to the far left is the default and returns a 1 by the Get(LastMessageChoice) function, then then goes to the right.

This is what the developer sees.

This is what the user sees when the script is executed.

The way the Get(LastMessageChoice) is used in a script is that after the Show Custom Dialog script step, the developer would include a set of branching IF statements. Depending on the result returned from the Get(LastMessageChoice), aka the choice made by the user via the dialog box, the script will perform a set of script steps.

This is what a typical script may look at using the above examples as context.