5 Replies Latest reply on Nov 24, 2010 8:36 PM by FentonJones

    New to Filemaker but not new to programming

    raykennedy

      Title

      New to Filemaker but not new to programming

      Post

      I have had the free trial download for the last couple of days and have a pretty good handle on the basics of the program. My first question is in relation to the flexibility of the relational tables. I understand how to use a portal and it's quite effective but it seems to limit your design capabilities across related tables. Maybe I am missing something but here is what I am trying to do. I am use to programming language that queries data and you can manipulate various fields across several tables. In Filemaker pro the only way it seems you can pull in certain tables into certain layouts is through a portal.

      I am trying to create a printable report that pulls various fields that are related from multiple tables and place them in unique areas on the page. Basically I have a certain format I have to follow for data reports for work and this would help streamline it. Assuming the tables are related to the invoice. I would like to pull customers name and put that at a certain spot on a page and from another table pull the property address and put that on a certain spot on the page. Is there a way you can  query data like you could in programs such as PHP/MySQL. I am very familiar with this structure so it's hard to get use to.

      Hope that makes sense and it's not too lengthy. Any tips or direction would be helpful.

        • 1. Re: New to Filemaker but not new to programming
          philmodjunk

          FileMaker certainly enables you to work with "various fields across several tables". The difference is that in other systems, you can construct SQL expressions "on the fly" that link your tables as needed where with FileMaker, you have to construct the same links in advance in the Relationship Graph. Given that you can use multiple table occurrences for the same data source table to establish as many different relationships as you need.

          Thus, portals can contain fields from more than one table, provided you've established the needed links in the Manage | Database | Relationships.

          And you can also place fields from several tables on the same layout by the same rules.

          • 2. Re: New to Filemaker but not new to programming
            BrianWilliams

            I am in the same boat as you Raymond.  Besides this forum and the excellent help you get from it, I reccommend a copy of "The Missing Manual" series for your version of Filemaker.  Very easy to understand, and covers the gamit of FM functionality.  My copy is a month old and well used.

            • 4. Re: New to Filemaker but not new to programming
              raykennedy

              So here is hopefully a more clear explanation of what I am attempting to do. Still researching this but any further direction would be helpful.

              Let me know if this is possible

               Table 1 : Transaction (real estate transactions)

              This will hold data such as contract terms associated with the transaction.

               Table 2: Customers (these are basically sellers & buyers and their contact and personal details) This needs to be separate because there could be various numbers of sellers and/or buyers. 

               Table 3: Principals (basically this will hold the transaction_id, the customer_id). The idea hear is I could add multiple buyers and sellers id to this table and associate them all with the same transaction id to be pulled later.

               The problem here is I am unsure how to pass the data from "PRINCIPALS" over to the transaction interface based off the common transaction_id that is saved in this table.

               I am use to other languages where you would use a query like "Search * tablename WHERE Principal Type="Seller" and _kp_transaction_id = "tra00001" based off the same setup. Than I would dynamically loop them and manipulate the layout with the found results.

               I would like to pull the date over to this interface and manipulate it. I have searched various resources but have not been able to find anything like this. 

              What I can do is pull data into an interface based off of one foreign key id (related tables). This only gives me the option of doing this once so if there are multiple sellers and buyers  with the same transaction_id this doesn't work.

               I also am able to pull in the portal if I set up my relationships differently but than I lose control over the ability to manipulate the layout

               Any help or assistance. Thanks again.

              • 5. Re: New to Filemaker but not new to programming
                FentonJones

                It sounds (to me) as if the structure is fine. The Principals table is basically a "join" table, between the Customers and the Transaction (with an ID from each those two tables, and possibly a unique auto-enter ID of its own, though not required at this point). Each person can have one of two "roles" in Principals, either "buyer" or "seller." 

                If you create what I think of as "dummy text calculations", that is a calculation field with the word "buyer" as its contents, and another with "seller" (both can be unstored, but it hardly matters). Then if you include each of those calculation fields in relationships, to two more "table occurrences" (the large boxes on the Relationship Graph, TO for short) of the Principals base table, then you can target only "buyers for this transaction" and "sellers for this transaction" separately. 

                In FileMaker 11 you could actually do this with only a filtered portal, not requiring the two calculation fields or extra relationships, if you only want this for display. But a real relationship can do more, like navigation and calculations (more on this later). If you have 11, try it with just the portal filter first and see if it does all you want. In that case the filter would be in the calculation you define for the portal (an option in the portal dialog). This is actually more like SQL, than the more "structural" older FileMaker method in the preceeding paragraph.

                You're already targeting Principals via the Transaction ID, so you'd just need to add a filter for:

                Principals::Role = "buyer"  // or "seller" on another portal.

                You can put those "buyer" and "seller" portals separately on the layout, wherever you want. Except you cannot put them where they might cross a page break. They can "slide up", which is a bit of science, a bit of black art (sometimes), to get text and other objects to slide up (the way you want, intellegently hopefully) on the printed page.

                There are also methods to "gather" the related records with several fields into a large text result calculation. If you have FileMaker 11 Advanced you can use a Custom Function for this (they can do calculations "recursively"); there's one named GetRows (also requires GetNthRow). From: http://www.briandunning.com/filemaker-custom-functions/list.php

                Using that with added tabs (typed into a text editor and pasted) within the calculation, you can then use tab stops within the calculated text, to appear much like a "table" of text (no lines, but otherwise looks OK). It can also more safely cross page breaks. Though, I must warn you, FileMaker 11 has serious problems right now with page breaks; it often cuts the last line in half (sliced horizontally). I've seen this both in Mac 10.6.4, and in Windows 7 (same machine). I imagine the next update will fix it, as it's well known, though who knows when).

                But there is it. FileMaker can do much of the same stuff, though it's a bit trickier to gather data from all over. But I have used that GetRow () function in several situations to do reliable printing over page breaks |except in 11 | And just regular portals do fine on a form.