8 Replies Latest reply on Jan 16, 2014 10:47 AM by DranLang

    Combining records from portals

    DranLang

      Title

      Combining records from portals

      Post

           Good afternoon everyone!  I have a question regarding portals, I have a layout that has three portals (OWN, RENT, LEASE) and all of these portals has the same fields (Part No, Make, Year).  Is there a way to have all the records from these 3 different portals placed in the same table or better yet when a record is created from these 3 portals, the same records will be created in another table?

            

           Thanks everyone, any help/ idea is highly appreciated.

        • 1. Re: Combining records from portals
          philmodjunk
               

                    Is there a way to have all the records from these 3 different portals placed in the same table

               While details not shared about how you would use this database could change my opinion, not only can they be from the same table, they should be from the same table.

               

                    or better yet when a record is created from these 3 portals, the same records will be created in another table?

               That does not sound like the best design approach to use, it would produce redundant data in two or more tables--something that relational databases are intended to prevent the need to do so.

               You could add a category field to your portal's where you select/enter the text "own", "rent" or "lease". Both Portal filters and calculation fields defined in the parent table used as match fields can be used to only show, create and edit records of one category in a given portal.

          • 2. Re: Combining records from portals
            DranLang

                 Our sales manager wants to have a separate table for rented materials, company owned materials and material we lease. 

                 I have a layout that needs a pull down list of all (materials from the three portals) the values of materials that the company owns, materials we rented and those that we lease.

                 Initially, I thought maybe we I should just combine them (the 3 tables used in the portals) by importing them to a single table.  Then our sales department wants to have the three table independent from each other because they have other use/method because we already have a system that uses the existing tables and records in them.

            • 3. Re: Combining records from portals
              philmodjunk

                   But your Sales Manager is not a database designer. You can give him his "tables" without having actual separate database tables in your database.

                   Never let a client tell you how to design the inner workings of your database. That can be difficult at times, but when discussing your design with them, the trick is to keep the focus on what they see on the screen and the printed page, not what you create "under the hood" to make it happen.

                   

                        Then our sales department wants to have the three table independent from each other because they have other use/method because we already have a system that uses the existing tables and records in them.

                   You should be able to modify that system so that it works from a single table. As an example, if you need to export the data to other systems, you can pull up different found sets of records for export--each a different category of records. As another example, if they have other layouts that are specific to one category, the design of these layouts can be modified such that they all access the same table, but scripts and custom menus prevent users from seeing any records except those for a specific category.

              • 4. Re: Combining records from portals
                DranLang

                     Agreed! 

                     Is there a way in this case to have a pull down list using a value list containing “Rented” only equipment and “Lease” only equipment as the contents of the value list from only one table?  I already have a layout that has a portal that has all these records from 3 different table (Rent, Own, Lease) but the challenge for me using this is I can’t find a way to have a pull down that has a value list specific only for rented materials, own materials and leased materials.

                • 5. Re: Combining records from portals
                  philmodjunk

                       a value list specific only for rented materials, own materials and leased materials.

                       That sounds like a conditional value list where you limit the list of possible values to only those for a particular category.

                       Brace yourself the "infodump truck" is now backing up towards you... wink

                       There's a lot of overlap between the first two links so you can read one and skim the other. The demo file gives you several examples of different conditional value lists so you can examine them to see how they are set up. If you are using FileMaker 12 or later, you can open this demo file from your File menu to get a converted copy of the demo file that you can examine in your version of FileMaker.

                       The next to last link discusses how to set up a chain of conditional value lists where the value selected in each conditional value list controls the values listed in the next value list and the very last link is a fairly new addition that describes how to use ExecuteSQL to produce conditional value lists that aren't easily possible with the other methods described here.

                       Forum Tutorial: Custom Value List?

                       Knowledgebase article: http://help.filemaker.com/app/answers/detail/a_id/5833/kw/conditional%20value%20list

                       Demo File: https://www.dropbox.com/s/j6qf0z9fnem3uxd/ConditionalValueListDemo.fp7

                       Hierarchical Conditional Value lists: Conditional Value List Question

                       Using ExecuteSQL to produce a conditional value list: Using ExecuteSQL to Produce a Conditional Value List

                       Feel free to post follow up questions here if those links aren't enough to get your value lists working correctly.

                  • 6. Re: Combining records from portals
                    DranLang

                         Thank you Phil for these links, very educating and helpful.  I was reading one of the feature of FileMaker is using PHP which lead me to a quick question that I want to ask but not related to the topic above, can we use FileMaker as database (the same way we use SQL in using visual studio programming languages or perhaps in using JAVA) for PHP and how?

                    • 7. Re: Combining records from portals
                      philmodjunk

                           PHP can be used to publish a FileMaker database to the web. This method is called "custom web publishing". That's about the limit of my understanding of PHP.

                           Since FileMaker can be set up as and ODBC data source, it would be possible to use a different application as the interface application for accessing the data via ODBC. This, however, sounds like a much more difficult option for designing the interface so I wouldn't try that option without a very good reason to do so.

                      • 8. Re: Combining records from portals
                        DranLang

                             Thanks again Phil!