7 Replies Latest reply on Nov 29, 2010 10:25 AM by philmodjunk

    Combine 2 Tables into 1 Portal?



      Combine 2 Tables into 1 Portal?


      Hi -

      Im getting on will with FM Pro 11, but need to know if the follow is posible using this example?:

      We sell products via 2 channels - Online and Offline (telephone orders etc)

      We raise offline orders in one table, and log online orders in another table.

      We have setup a "Dashboard" layout which is essentially a portal which shows all "Active" orders from the Offline orders table.

      I would like to have the portal show all active orders from both tables - can this be done?

      This way, we can have a good overview of all active orders in the company for people to action.

      Currently, we have it set to remove any record from the portal (via filter) to not show any closed/delivered orders.

      Any assistance welcome.

        • 1. Re: Combine 2 Tables into 1 Portal?

          My opinion. Both are just Orders. They belong in the same table. The fact that one is "online" is just a type field, a "parameter" of the order. You could separate them fairly easily in portals for data entry (if you want to, sounds like you don't); you could give each its own dedicated Tab or Layout.

          It is far more difficult to bring two tables of what is basically the same "entity" together later than it is to present two variations of the entity separately for data entry or reporting. Relational design is based more on what objects really are. These are "orders", "online" or "offline" are just different types of an order; the only object is an "order".

          They would then be available together for all kinds of normal operations, such as adding up the money, separating by date, etc., while remaining separate for data entry (into filtered portal) if desired, or summarized separately for a report; and of course for Finds.

          If you absolutely need to see each separately in a List view of their own table, without a Find (though a Constrain Find on type could be triggered automatically), then you need a expanded method. There is a structural method, which is known as the David Graham method (after its author), which would use 3 tables: 2 as you already have, and a 3rd "central" table, with a record for each record of the 2 existing tables. It is a bit more work, but gives maximum flexibility. However I don't think it's really needed in your case.

          • 2. Re: Combine 2 Tables into 1 Portal?

            Thanks Fenton.

            Perhaps I should be more specific..... (i'll try)

            We manually key in a full "offline" orders from customers, this is a vast record of product(s), purchase notes, history.

            For an online order, the details are kept online, all details already exist, we dont need to re-create it, but merely log its basic details.

            We would like to punch in the Online order reference, eg:

            1/12/2010 - O/N 1005, Mrs Smith, Paperback Book, Del Date 5/12/2010.

            This would populate a pre-filling portal of "offline" orders to create our live order portal - which is what we need to see throughout the day for staff to pick, look at, process etc.  its to centralise all orders into one viewing window essentially.

            We cannot use the "offline" table to log "online" jobs as it will allocate a reference which we cannot mixup.

            • 3. Re: Combine 2 Tables into 1 Portal?

              We cannot use the "offline" table to log "online" jobs as it will allocate a reference which we cannot mixup.

              I think you need to explain what you mean by that. From here there looks like there are no major issues that argue against combining the order records in the same table. As for data that is only collected for offline orders, those fields can be left blank for online orders, or those details can be placed in a "detail" table with a one to one relationship to your orders table. Off line orders would have a detail table, online would not.

              While using the same table for both types of orders, it's possible to have different layouts for each type of order, separate reports. Different portals, etc. Scripted finds could easily keep the two kinds of orders separate when they need to be separate and yet you now have the option of also working with them in the same report when that is needed.

              • 4. Re: Combine 2 Tables into 1 Portal?

                When we Create a new record in the current "Offline" table it automatically creates an order number - this is eg "OFF1234"

                These must be sequential and relate to the offline order.

                If I was to log a few online orders into this same table, then the offline order numbers would be muddled as new "online" logs would be utilising automatically generated offline order/invoice numbers.

                Your theory is right though, only if I didnt need to rely on sequential numbering of the records, it would work.

                I hope this makes sense.... so is there a way of doing it, based on my requirements to keep them seperate?

                • 5. Re: Combine 2 Tables into 1 Portal?

                  Yes, there is a way to keep them separate and see them together when necessary. It is called the "David Graham" method, after its author. It requires a 3rd table, with a record for each, and a 1-to-1 relationship to each of the two tables. Each record in the "common" table would link to either one table or the other.

                  You would need a mechanism however to pass the statuses you need to filter by, since they'd need to be present in the common table. They do not happen at creation time (as the ID and creation Timestamp do), but later. (FileMaker 11 could filter the portal via a calculation looking at each table; but that would be slower.)

                  You have not really said what the "online" system is, whether it is the SAME FileMaker file, or something else. If it is the same file, I would put them both in the same table, and find some way to create your "sequential" numbers for each type of order. Though I do not really see why they need separate data in such a field. I would not use such a field as an "ID" however, not for relationships; I'd use a real FileMaker auto-enter ID.

                  • 6. Re: Combine 2 Tables into 1 Portal?

                    As mentioned in my OP the online items are online sales of products from varying sales outlets, these are:

                    Our own WWW website

                    Obviously these sales outlets (especially ebay and amazon) create their own order IDs which we use to track orders which clients may contact us about.

                    The idea of the portal system as mentioned, it to simply log into a filemaker database to create a live snapshot of current orders from ALL sales channels (including offline)

                    The status of an order is either Live, or Complete - so filtering I would assume will be very simple.

                    To move this on, these are the fields I would like in the "online" table for logging live orders:

                    Order Date / Online Ref / Customer Name / Delivery Date / Supplier Name / Order Description / Date Confirmed / Date Customer Delivery advised / Completion Date

                    The offline table has all of those and about 50 more fields due to the amount of detail.

                    Any info welcome.

                    • 7. Re: Combine 2 Tables into 1 Portal?

                      When we Create a new record in the current "Offline" table it automatically creates an order number - this is eg "OFF1234"

                      These must be sequential and relate to the offline order.

                      This order number may be created automatically in that "detail table" I referred to earlier. Since only Offline orders would have this detail record, only offline orders would have this order number.

                      Like Fenton, I recommend that you not use this number as a primary key for your orders, but rather as a "label" value used to find, sort, and identify specific records.