1 2 Previous Next 16 Replies Latest reply on May 17, 2011 9:50 AM by SharonSickler

    How to Create a Relevant Datasheet



      How to Create a Relevant Datasheet



      I'm using FMP 11 for Windows, and I have a database that stores information about customer equipment that we service (make, model, serial, etc.). Each unit (A/C, Water Heater, Air Handler, etc.) has it's own tab with the same type of fields.  I want to create a layout that shows all the applicable equipment info for the customer.  So for example, John Smith has an A/C unit of Make X, Model Y, and an Air Handler of Make A, Model B, but he doesn't have a Water Heater.  I want to be able to print a datasheet for John Smith that shows the info for the units he has, but doesn't show all the blank fields for those he doesn't.



        • 1. Re: How to Create a Relevant Datasheet

          Looks like each item of equipment that belongs to John Smith should be a separate record in an equipment table that is then linked via a relationship to John Smith's contact record by contact ID.

          A data sheet can then be printed from a layout to the Equipment table that will only list the equipment that belongs to him.

          It will take a number of changes to get this to work with the layout you have as Tab controls aren't the most convenient method to use for such a setup. It can be done, but you'll need either a numer of relationships for each type of equipment or some filtered portals so that the correct Equipment Record is displayed on the correct tab.

          • 2. Re: How to Create a Relevant Datasheet

            You don't think there's an easier way to do this?  Some sort of calculation like "if A/C Make is not empty, display (all A/C info)" ?

            • 3. Re: How to Create a Relevant Datasheet

              THere are different ways to do this, but I wouldn't call them easier and the alternatives are not near as good a set up in terms of overall database design. Using separate records for each item of equipment also permits many more options for how you work with your database than the current "flat file" design.

              • 4. Re: How to Create a Relevant Datasheet

                Okay, I created a new table, and I think you're right; it will work better.  However, I'm still having a little trouble getting a print layout design that will show one customer's equipment, hiding empty fields.  I tried using the report option to sort by customer id and then grouping by equipment type, but that doesn't work.  Is there a way to do this?

                • 5. Re: How to Create a Relevant Datasheet

                  One thing that I don't know is how similar/different the data fields are for each type of equipment. If they are the same, a list layout can be configured where you either perform a find for a specific client or use Go To Related Records from a script that starts from the client record on your client layout to pull up just the equipment records for that client. Then all that's left is to print the report that you want.

                  If the different records require different fields, you may need to work with Sliding fields and/or conditional formats to customize the look of each record in your report layout to match the needs of each equipment type.

                  • 6. Re: How to Create a Relevant Datasheet

                    The data fields are exactly the same for the different equipment.

                    Thank you; I didn't think of using a scripted find to filter the database.  Now, just one last question; how can I print a datasheet for John Smith that shows the info for the units he  has, but doesn't show all the blank fields for those he doesn't.  I've tried sliding fields, but the titles don't collapse.  What am I missing?

                    • 7. Re: How to Create a Relevant Datasheet

                      Okay, using the idea from your answer Dynamic selection of fields to print, I am making a report where all the titles are calculated using some variant of this:

                      If(not IsEmpty(Make); "Make"; "").  Unfortunately this evaluates to "" when even one record's Make field is empty. 

                      How can I write a calculation that will display the title even when only one record's Make field is not empty?

                      • 8. Re: How to Create a Relevant Datasheet

                        You shouldn't need to take that approach.

                        You should have these two tables with this relationship:

                        Clients::ClientID = Equipment::ClientID

                        Each time you record information on a different piece of equipment for a given client, you create a new record in Equipment for that new piece of equipment. If John Smith has  a Boiler and a Heat Pump, you create two and only two records in the Equipment table and link them by ClientID.

                        Now you can create a data sheet on the Equipment layout, adding fields from Clients as needed to fill in needed information about the client. When you perform a find for a given client on the equipment layout, you will find all equipment records for that client. Since the fields are the same, the only fields you'll see in the data sheet are those for equipment that client owns. If John Smith doesn't own an Air Handler, there won't be a record in the table for an Air Handler, and thus no empty fields for it appear in the data sheet report.

                        Here's a demo file:  http://www.4shared.com/file/ZC7KbKV3/ClientEquipmentList.html

                        It's very basic, showing only two types of equipment, but should give you the idea.

                        Key details:

                        1. "Allow creation of records via this relationship" as been selected for the equipment table in the portal's relationship. This makes it possible to add new equipment records just by entering data into one of the portals if no data has yet been entered.
                        2. Equipment Type is used as a Filter on the two portals so that each tab shows only records for equipment of that type. An Auto enter calculation enters the current value of $$EquipmentType into the equipment type field so that the record shows up on the correct file tab. There's an onobjectEnter script trigger on both portals that passes the portal's equipment type as a script parameter and stores it in $$EquipmentType.
                        3. The DataSheet button performs a script that uses Go To Related Record instead of a find to find all records for the current client and displays them in a sorted list.
                        4. This demo only works with FileMaker 11.


                        You may find it much easier to set up if you use a single portal to all your equipment instead of separate dedicated tabs. That would make it easier, for example to log information for a client that owns two or more items of equipment of the same type.

                        • 9. Re: How to Create a Relevant Datasheet

                          I have that relationship between the tables; each piece of equipment is stored as its own record.  However, each unit has characteristics; make, model, location, replacement part1,2,3, etc., and not all units have data in all of these fields.  It's these sometimes unused fields I want to hide.

                          I've figured out how to make the titles slide by evaluating whether a field count function is non-zero, but now none of the columns line up.  Is there a way to set tab stops for sliding?

                          This does seem to me to be a convoluted solution; if there's another table I need to set up or something, I'd be happy to.  This is just the only way I could think to get close to what I want.

                          Thanks for your help and patience so far!  I really appreciate it.

                          • 10. Re: How to Create a Relevant Datasheet

                            Can you post an example of what you want your report to look like. Sliding may not be needed. You may just need to use conditional formats or other visibility tricks to hide the fields without anything sliding.

                            • 11. Re: How to Create a Relevant Datasheet

                              Okay, pics located here.  I'm using excel to mock up the report.  See how serial and product # are gone in the second picture?

                              • 12. Re: How to Create a Relevant Datasheet

                                You mean the field labels at the top of those columns are gone?

                                This looks like something that you can list in a standard table view of your equipment layout. That would leave all the column headers, but it would be fairly easy to hide any columns that aren't used manually after you pull up this view of a client's data.

                                Doing this automatically will be difficult to achieve. In fact, I'm not sure that you can. You can set things to slide left on a list view layout, but you can't control sliding left to make sure that the field only slides when the field is blank for all records in the data sheet.

                                • 13. Re: How to Create a Relevant Datasheet

                                  But that's exactly what I've done, via a hidden summary cell that counts items in the field in the set of found records, using calculated fields instead of column labels.  My only problem now is that the fields aren't aligned between records.  I have something like this:

                                  Type  Make  Model  Location  

                                  AC  Huston  Smartsaver  Under Deck

                                  Water Heater   United     Waterwitch    Bathroom

                                  I figure if there's a way to set tab stops, for the sliders, then they'll align more neatly.

                                  • 14. Re: How to Create a Relevant Datasheet

                                    Yes, but no such tab stop settings exist with sliding fields.

                                    Hmmm, you might be able to do this with a calculation field (set it to return text) written like this:

                                    Substitute ( List ( field1 ; field2 ; field 3 ; //and so forth ) ; ¶ ; Char ( 9 ) )

                                    List will produce a list of the field's data, but with carriage returns separating them. Substitute then replaces the returns with tab characters. Now in the tabs section of the inspector's appearnance tab, you can set tab stops for this field.

                                    1 2 Previous Next