1 2 3 Previous Next 40 Replies Latest reply on May 14, 2012 2:15 AM by GuyStevens

    Getting the layouts to work with join tables

    PeterThorton

      Title

      Getting the layouts to work with join tables

      Post

      Hello,

      I've been having some difficulties getting Filemaker Pro to work the way I need.

      [I have software development background - I've worked as a .NET C# and Java programmer for some time, I have extensive experience with the MS SQL Server. So I know how relational databases work.]

      I'm battling with the FileMaker user interface - the layouts.
      What I need to do is pretty straightforward. I have a table called Product, which stores all the product info, and a table called Order, which will store orders. Since one order can contain many products, and one product can be on many orders, it's a many to many relationship. SO I added a join table called OrderItem. Its fields are pretty self explanatory:

      ProductID
      OrderID
      Amount
      Price

      I've attached a diagram.



      I've set up the relationships, everything should be in order on the data model side. Now I need to create a layout that will allow the user to create an order and put order items on it. I've made a layout based on the Order table, and put a portal on it, now I can create new OrderItem entries on the layout. But that's not quite what I want the user to see.

      I want the user to be able to pick a product by ProductName, from a context menu/popup window or something like that. There's actually a lot of products, so navigating to a separate form would be acceptable. The ProductID of the selected product should be entered into the OrderItem's ProductID attribute, but the corresponding field should display the ProductName. I'd also like the Price field to be pre-filled when the user selects a product, but the user must be able to edit the field without rewriting the original value in the Product table. In other words, the user needs to be able to put in special price just for the specific order.

      I realize this is pretty basic stuff, but I have almost zero experience with FileMaker, and I'm kind of stuck. If I could get help figuring this one out, it would be a huge boost, because most of the other situations in my database are exactly or pretty much the same.
      Thanks in advance for any help.

      OrderItem.png

        • 1. Re: Getting the layouts to work with join tables
          GuyStevens

          I think you are pretty much on the right track.

          I just passed by this link and checked it out. It's a little demo file that, I think, explains exactly what you need.

          http://fmforums.com/forum/topic/63425-auto-fill-one-field-with-text-from-two-fields/#entry300150

          For the price to be pre filled but changeable you would use a lookup field for the Price field in your order item table. That lookup field looks up the price in the Products table.

          If you need any more help, feel free to ask. All the things you listed can be done prette easily. But for someone who doesn't have any FM experience it might be a little tricky to get started.

          • 2. Re: Getting the layouts to work with join tables
            GuyStevens

            Also, Please don't forget to use ID Fields in every Table.

            Your orderItem table doesn't seem to have it's own ID.

            • 3. Re: Getting the layouts to work with join tables
              PeterThorton

              Thanks, the second sample file in that forum thread does exactly what I need to do. The problem is it's a final product, not a howto or a tutorial, so I have no idea how to achieve that result just by looking at the file. I'll try to sort of 'reverse engineer' it, but it's gonna be a struggle. If you could provide any info on how to proceed to get to that result, I'd be grateful.

              All my tables have an ID in the filemaker database. The OrderItem table on the diagram doesn't have an ID, because I just hastily sketched it in order to illustrate my problem.

              • 4. Re: Getting the layouts to work with join tables
                GuyStevens

                How are you getting along? Are you managing to recreate the example file?

                It's a bit much to try and start to explain the entire step by step process of creting such a file, so if you have any specific problems, let us know.

                • 5. Re: Getting the layouts to work with join tables
                  PeterThorton

                  Hi,

                  sorry for the late response, I got shuffled around the office a bit, I just got back to working with Filemaker yesterday. I was able to get my file to work exactly the same way as the sample file.

                  However, I'm facing another problem, and this one might be a bit complicated. I'll try to describe my situation:

                  I have an Order table, and a Customers table. It's one customer per order, and each customer can have multiple orders, so the Order table has a foreign key that points to CustomerID in Customers. Pretty straightforward.

                  Then I relized that if the address (or the customer name) in the Customers table changes, the data will also change in all the previusly made orders. So I needed to make lookup fields and just copy the information for each individual order. Since it's quite a lot of fields (each customer has their business address and potentially a delivery addres, so we're talking 12 fields) and I didn't want them cluttering up the Order table, I made a separate table that connects Order with Customers and has lookup fields for all the data that has to be stored for the order. I called it CustomerLookup, I marked it green on the diagram. I made a similar table for the Dealers. It may not be the best approach, but it's transparent enough and it works. When I pick a customer on the Order form, Filemaker pre-fills the customer's address, which is very helpful.

                  But the hurdle doesn't end there. Not only does the customer have a main address and a delivery address, but due to the location of my company and the nature of the business, many customers need to have their address in both English and Japanese (and in the future some of them are likely to have their address in Chinese or German) This is not just a matter of arranging the various bits of the address differently on a form. Japanese uses its own writing system, as does Chinese, so the addresses need to be stored separately.

                  Two different addresses and 4 different languages potentially mean 8 different addresses. I'm not making a table with 48 address fields. So my thinking was, make an Address table. It will be connected to the Customers table, so each customer can have as many addresses as needed. The table would have a 'type' field and a 'language' field, that would determine which address would be used in any given case. The new Address table is marked red, on the left side of the diagram. Here are some complications I've run into so far:

                  1. I can't use lookups now, can I? Before, the CustomerLookup table just looked up all the values from the Customers table. But now that the address is in a separate table, and there can be multiple Address instances for each customer, how will the lookup functionality know which value to use? And if lookups won't work, how else am I gonna get the data into my CustomerLookup table?

                  2. I need my forms and reports to pick certain instances of the address based on the values of the attributes. For example, I want the 'Japanese invoice' report to always pick the Japanese address. And I'd like the general 'Input Order' form to pre-fill the English address, but give the user the option to pick from all the available languages.

                  I guess these two points are pretty much the same, I'm just still used to thinking about the data layer and the presentation layer separately. In SQL I'd just write

                  SELECT * FROM CustomerAddress WHERE CustomerID = @CustomerID AND Language='Japanese'

                  but how to achieve this using Filemaker's interface?

                  3. I would like my forms to do the following:

                  Use the main address, and if there is a delivery address, include it too.

                  This would mean creating two records in the CustomerLookup table instead of just one. I'd like my forms to do this automatically, so the user won't have to check if the customer has specified a delivery address every single time.

                  Any advice will be greatly appreciated :-)

                  • 6. Re: Getting the layouts to work with join tables
                    GuyStevens

                    For the adresses I think the adress table is a good idea.
                    I would never delete an adress in that table. If the adress changes I would just make a new record in the Adress table and point to that.

                    If you want you could add a field where you could mark an old adress as "Outdated". So your adress would still appear on the old orders.

                    Then to keep the old adress from showing up you could make a global field in your customers table that has the same value as the "Outdated" field in your Adress table and make a relationship of "Is not equal" to the "Outdated" Field.
                    So when an old Adress is marked "Outdated" it doesn't show.

                    CustomerAdress::CustomerID -------[Is Equal)]--------Customers::CustomerID
                    CustomerAdress::Outdated -------[Is Not Equal)]--------Customers::globalOutdated

                    All the rest about the languages can also be done using relationships.
                    Create a language field in your Adress table and create a language field on your Order table.
                    Make a second table occurrence of your table "CustomerAdress" (Use the icon in the relationship dialogue with the two green plus signs. Then double click it to rename it "CustomerAdress_Order")
                    And make relationships to the order field.

                    Link the Customer Id,
                    Link the Language field ,
                    make a global field "Outdated" in your Order table and make it an "Is Not Equal" relationship. Set this global field to have the same value as your "Outdated" field in your Adress table.
                    You could also add a "Type" field for "Business Adress" and "Delivery Adress"

                    CustomerAdress_Order::CustomerId-------[Is Equal]-------Order::CustomerId
                    CustomerAdress_Order::Language-------[Is Equal]-------Order::Language
                    CustomerAdress_Order::Type-------[Is Equal]-------Order::AdressType
                    CustomerAdress_Order::Outdated-------[Is NOT Equal]-------Order::Global_Outdated

                    That way on the Order you select a client first. Then a Language, Then maybe even a Type and then only the adresses show up that are correct for that relationship.

                    I hope this makes sense.

                    • 7. Re: Getting the layouts to work with join tables
                      GuyStevens

                      I just noticed that in my example I completely ignored the Customer Lookup table. But you might not need it if you do it my way.

                      Just play around with it, see what you think.

                      • 8. Re: Getting the layouts to work with join tables
                        PeterThorton

                        Hi,

                        I'm not sure I'm following you. Do you think you could sketch up a simple diagram for me? (if it won't take too much of your time)

                        I'll admit, I still don't understand a lot of FileMaker specific features, like the multiple table occurences, and the 'Is Equal' and 'Is Not Equal' relationships. I'm gonna read up on those things right now.

                        But if I understand you correctly, what you are proposing is that the Order table will actually link to the CustomerAddress table for information about the customer's address. All addresses would be kept, if the address is even slightly changed, a new record is created. I can see how that would dramatically reduce duplicity. If I do it my way, if I have say 50 orders for one address and 10 orders for the second address, I'd have to store the first address 50 times and the second address 10 times. This way I only have to store each address once.

                        This would make the user interface a bit more complex, I'd have to make sure that every time the user changes the customer's address, a new record is created. And what if the user is just fixing a typo, and I actually want the address on the old orders to change?

                        Also, an Order could potentially have many addresses associated with them, due to multiple language variants. Most of them will probably have at least a Japanese and an English one. This will probably call for another join table. It's getting mighty complicated mighty fast.

                        And just a few minutes ago a coworker matter-of-factly mentioned to me "Oh by the way, we have two warehouses and we need to track which one the products are in." :-D

                        • 9. Re: Getting the layouts to work with join tables
                          GuyStevens

                          Hey Peter, I'm making you a little example file.

                          I just had a question concerning the Language.

                          Do you first select a language? And then the adress?

                          Or dou you print your forms in a different language depending on the language given with the adress.

                          Meaning, if a client with a Japanese adress wants to make an order, then don't you just make a Japanese order form?

                          Or do you first select the language and then the adress that corresponds with that language?

                          In other words: what is your normal workflow?

                          I have to do some work now but I will finish your example later.

                          • 10. Re: Getting the layouts to work with join tables
                            PeterThorton

                            Hi,

                            thanks again for your help. I believe we'll have different forms for different languages. The primary language for the forms will be Japanese, as far as I know we'll have an English copy of every form that needs to be in English as well. But the "manage customer" form has to allow the user to input both addresses, and specify which one is which.

                            • 11. Re: Getting the layouts to work with join tables
                              GuyStevens

                              http://dl.dropbox.com/u/18099008/Demo_Files/Invoice_MultiAdress_DaSaint.fp7

                              Here is a little basic demo file i've done.

                              You might be a little overwhelmed by the multiple table occurrences, especially if you don't know how that works.

                              I'll try and explain a little:

                              You make one table. For instance the table "Tbl_Adress" like in your case.
                              And you create a relationship betheen "Tbl_Customer" and "Tbl_Adress" to give multiple adresses to every customer.

                              A little while later you want to be able to select a certain adress on your invoice.

                              You might need particular relationships that exclude certain adresses but include the correct ones.

                              In that case you can make a second Table Occurrence of your "Adress" table.
                              You are not creating a new table. You still only have one table. All your data is still only in one place.

                              But on this second table occurrence you can make all kinds of different relationships.

                              For instance you select a client on your order, so the adress needs to be for that client: (Tbl_Adress::ClientId -----[=]-----TblOrder::ClienIdFk)
                              Then you select a language on your order, so the adress needs to be for that language. (Tbl_Adress::Language -----[=]-----TblOrder::Language)

                              Then the Order and Billing Adress fields are checkbox fields that set a value of "Yes"
                              In order to find the Shipping Adress I have created a global field in the Order Table that has a value of "Yes"
                              Because it is global it holds one value (Yes) for every record in the table "Order"
                              Then you can use this value to find related records in other tables.

                              In other words. To find the Billing Adress the field "BillingAdress" in the table "Tbl_Adress" needs to have a value of "Yes".
                              So for that table occurrence I make a relationship like this: (Tbl_Adress::BillingAdress -----[=]-----TblOrder::Global_Yes)

                              The field "Global_Yes" always has a value of Yes. Therefore I will only see adresses that have a value of "Yes" in the "Billing Adress" field.

                              Anyway, if you have any more questions, just shoot.

                              Have a look at the example and study it a little.
                              Of course this is just very basic, and you can go as far as you want with this.

                              • 12. Re: Getting the layouts to work with join tables
                                PeterThorton

                                Hi,

                                thank you, that was very helpful. (never mind it's been three weeks, I've had a week long vacation and two weeks of doing something else) I believe I'll be able to implement the basic functionality I want, thanks to your example. But as for user interaction, I want something slightly different. I want the user to be only able to see the actual address, when editing customer info, not a table containing all of them with checkboxes. When the user clicks 'change address', I want Filemaker to set all the old addresses (in the given language) as outdated (this will probably require a script), and insert a new record with the new value. User should be able to pick which language verison to view via a dropdown list. When adding a new address, the user should also be provided with a choice of language, and if there already is an address in the language he chooses, when he enters the new one, the old one should again be invalidated. I'll try to figure this out on my own, but as I'm new to scripting in Filemaker, any help would be appreciated.

                                • 13. Re: Getting the layouts to work with join tables
                                  GuyStevens

                                  Are you saying you only want to show one adress with a language switcher to be able to select a language and see the adress for that language?

                                  Do you have situations where the shipping and the billing adress is different? If so, which adress do you want to display?

                                  For that reason I thought the portal would be handy, to get a quick overview of all of the adresses.

                                  If you want you could hide the outdated adresses from the portal.

                                  If that's not sufficient, let me know a little more about how you want it to be done.

                                  • 14. Re: Getting the layouts to work with join tables
                                    PeterThorton

                                    Are you saying you only want to show one adress with a language switcher to be able to select a language and see the adress for that language?

                                    Yes.

                                    Do you have situations where the shipping and the billing adress is different? If so, which adress do you want to display?

                                    Yes, there willl be such situations. Where that's the case, I want the user to be able to see that the customer has a billing address, and I want to give him the option to include it. There will be a field in the Order table that will specify "this order is to be shipped to this shipping address", so that the address will print on all the relevant forms along with the business address.

                                    For that reason I thought the portal would be handy, to get a quick overview of all of the adresses.

                                    If you want you could hide the outdated adresses from the portal.

                                    This may well be the case. I'll try the portals right after I've finished this post.

                                    I've included a screenshot explaining what I want to achieve. Here's the description of the elements:

                                    A.  The tab control represents the "Manage customer" functionality. The open tab should show the main address (or business address) for the customer.

                                      1. A language picker. It can be a dropdown box, which shows only the languages for which a business address is present. (I have a table called Language, with one attribute that just has values 'English', 'Japanese', 'Chinese', 'German', I'm not sure this table will even be necessary)

                                      2. The address field. I want the appropriate address to be displayed here when the user picks the language in the language picker. Ideally these fields should be non-editable.

                                      3. This button should display a new layout, where the user can update the address.

                                      4. The same functionality, except that this will be for the delivery address. The difference in the data is that the Address records have the "Type" field set to "DeliveryAddress"

                                    B. This form should allow the user to update the customer's address. If an address has been selected in the previous layout, it should be pre-filled here.

                                      1. Type - this will be filled in automatically, depending on which layout the form is called from. I just included it so I could see if it's working properly.

                                      2. The user should be able to pick any language that is in the system (either in the Language table or in some manually defined value list)

                                      3. fields for the address

                                      4. This is where it gets tricky. As we have discussed before, the existing address in the system should NEVER be deleted, and it should never be rewritten with a new one. (otherwise the data on older order sheets and invoices will change, which is not good) So what I need to do is set all the existing addresses for the given customer, with the same language and type as "Outdated", and then enter the new address as "Active".

                                      5. This should discard the new address and close the form (or navigate away from it back to where it was displayed from)

                                     

                                    I'm not entirely sure all of this is possible in Filemaker. I truly hope it is, otherwise I'm in trouble.

                                    1 2 3 Previous Next