10 Replies Latest reply on Jul 26, 2013 7:14 AM by philmodjunk

    Copy data calc troubleshooting

    SamRich

      Title

      Copy data calc troubleshooting

      Post

           I used this post to help me copy data from one table to another: Auto Fill

           One method it explains is the Lookup - which I have used without problem. The other is the auto-calculation via another table, which looks simple, but I'm having problems. 

           My tables are Contacts, Products, Orders, Order details and payments. I have a Paid/Unpaid field in Orders that I want copied to Order details, via a calculation. Orders are linked to Order details via Order ID (one to many). Contacts are linked to Orders, and Products are linked to Order details.

           When I try the calc, the field stays blank - and stays blank even when I try it for another field in the same table. Can anyone give me an idea as to why this might be? 

           Looking at the relationships I wonder if this might be the issue. I notice FM says I have a many to many relationship for Contacts to Orders - and I'm not sure why this is either. It also says I have many to many fro Products to Order details - but basically one product should be listed in each order detail entry. Could this be the issue and how do I resolve it? 

            Any clues greatly appreciated. 

            

        • 1. Re: Copy data calc troubleshooting
          philmodjunk

               There are two options in that thread. Looked up value settings that copy data from a related table occurrence and a second method that simply entails placing the fields from the related table on your layout.

               Neither use an "auto-calculation", nor any calculation at all unless one is needed in a match field in order to link to the correct record.

               

                    Looking at the relationships I wonder if this might be the issue. I notice FM says I have a many to many relationship for Contacts to Orders

               FileMaker has no real way to know, 100% of the time, whether or not a given relationship is one to many, many to one, or.... It displays what type of relationship is possible from the field options specfiied for the match fields used in the relationship. If a match field auto-enters a serial number or has a unique values validatition set in field options, you'll see the single line indicator for the "one" in a one to one or one to many relationship. If you do not specify one of those options and the field is stored/indexed, you'll see crows feet indicating that multiple matching values are possible on that side of the relationship.

          • 2. Re: Copy data calc troubleshooting
            SamRich

                 Sorry I wasn't clear in my original post. I tried to follow the first set of instructions. Under Order Details, I created a new field, named it payment status, chose auto enter calculated value, specified Orders:Paid. I then expected the field to be filled with Paid/ Unpaid data, but instead they remained empty. Any suggestions on where I went wrong? 

                 Thanks for the explanation about FMs display of relationships. 

            • 3. Re: Copy data calc troubleshooting
              philmodjunk

                   Yet the thread specifies using the looked up value setting. The auto-entered calculation option should work the same though it then will not update via the relookup option like a looked up value can be updated--which may not be a problem for your particular use, but that's the main difference between the two.

                   Are you testing this with a record created after you set up the auto-entered calculation? Existing records will not automatically update to copy over data from the related table when you add an auto-entered field option.

                   If a new record fails to copy over the data, then you'll need to examine the details of your relationship and the values present in the relevant match fields. One quick test is to temporarily employ the other option and add the field right next to the field where you have your field with the auto-enter option. If it remains empty, that's a pretty clear indicator that the relationship is failing to match to the record you expect to supply data during the auto-enter operation.

              • 4. Re: Copy data calc troubleshooting
                SamRich

                     Phil, thanks for your patience - sorry you're right, I used a method that wasn't discussed in the thread I'd pointed to. I was trying without success to avoid a long winded explanation - and ended up just being confusing. 

                     You're hunch was right, I was testing this on existing records - which were not automatically updating. So thanks for expalining that issue. 

                     But I was hoping for a way of copying live data from one table to another - so that when I mark an Order as Paid, simultaneously the associated Order Details items are marked as paid. (If you're wondering why I'm marking items as paid manually, it's because payments are by consignment and do not tally easily with orders.)

                     Why am I doing this? I have a layout by Customer that displays all the Order Details in a portal, so that I can see what Products they've ordered and how many, at what cost. I have a calculation on the main part of the layout that shows how much they've paid, how much they've been billed, and any balance. I used conditional formatting to make outstanding balance show up red. So far so good.

                     But now I want to make conditional formatting to show which Order Details entries have not yet been paid for. My unpaid/paid field relates to the Orders layout. When I tried to create a formula to make the Order Details relating to Orders that were unpaid appear red, it applied inconsistently. It worked when a customer had made only one order, and didn't work at all if they'd made more than one order.

                     I thought the solution to this would be to add an auto enter calculation field to Order Details to copy the (live) data from Orders. But now that I can't work out a way of doing this, I'm stuck...

                      

                      

                      

                • 5. Re: Copy data calc troubleshooting
                  philmodjunk

                       No, you would not use an auto-enter calculation for this. Changes in the look up table will not automatically update with looked up value nor with auto-entered calculation options.

                       Go back to my original suggested thread and look at the other option. This option is intended for what you describe. Instead of having two "paid" fields in both tables, you have just one such field and place that same field on layouts based on either of the two related tables. When you mark the field in ORders as Paid, layouts based on Order Details can use the same field to show that the item is from a paid order.

                       Your conditional formatting will  use the same prinicpals, but you'll need to make sure you have the correct relationships in place to support it. In a portal to Order Details, a conditional format expression can refer to the "paid" field in Orders to determine what format is used with that layout item.

                  • 6. Re: Copy data calc troubleshooting
                    SamRich

                         Phil

                         Thanks again for your patience on this. I understand about using the same field in layouts relating to different tables, and have done that successfully. So the nub of the issue is that I have a:

                           
                    •           Company table relating to an Order table by Company Name.
                    •      
                    •           Order table relating to Order details by Order ID.
                    •      
                    •           Products table relating to Order details by Product Name.

                         In a layout based on the Company table, I have a portal to Order details. In the cost of the order detail, I put red conditional formatting under the condition - Formula is: Orders::Paid = "Unpaid"

                         I was expecting Unpaid items to appear in red ink. This does work for companies that have only made one order, and that order is unpaid. But for companies that have made several orders, and some have been unpaid, it doesn't work at all. 

                         Any ideas where I went wrong? 

                          

                    • 7. Re: Copy data calc troubleshooting
                      philmodjunk
                             
                      •           Company table relating to an Order table by Company Name.-->Not the best approach, company names change and are not unique
                      •      
                      •           Order table relating to Order details by Order ID.
                      •      
                      •           Products table relating to Order details by Product Name.

                           But what relationship do you have between orders and order details?

                           I have been assuming these relationships:

                           Companies------<Orders------<Order Details>------Products

                           To get your conditional formatting to work, you'll need an additional relationship between Order Details and Orders using a new Table Occurrence of the Orders table:

                           Companies----<Orders-----<Order Details>-------Orders 2

                           Then your conditional format expression can be Orders 2::Paid = "Unpaid"

                           In Manage | Database | relationships, make a new table occurrence of Orders by clicking it and then clicking the duplicate button (2 green plus signs). You can double click the new occurrence box to get a dialog to appear where you can rename the new occurrence box if you want.

                           We have not duplicated a table. Instead, this is a new reference to the same table already present in your database.

                           Select the same matchfields for Order Details to Orders 2 that you have specified for Orders to Order Details.

                      • 8. Re: Copy data calc troubleshooting
                        SamRich

                             Phil

                             Woohoo! It worked. Although have to admit this was like magic to me, I've no idea why. Can you point me to a thread about duplicating tables, and when and why this is necessary?

                             Thanks again. 

                        • 9. Re: Copy data calc troubleshooting
                          SamRich

                               Phil

                               If I change my match field from Company Name to Company ID, won't it make it difficult for users? As I understand it, they'll have to have a reference chart of serial IDs for each company, instead of being able to just choose the name from a drop down list. Or is there a way round this? 

                               Thanks again. 

                          • 10. Re: Copy data calc troubleshooting
                            philmodjunk

                                 For more on Table Occurrences, see: Tutorial: What are Table Occurrences?

                                 With regards to using a company ID. If you set it up correctly, the users need not even known that company ID numbers exist.

                                 Option 1: Set up a value list with the "use values from a field" option. List the company ID number for field 1, then select the "also use values from" check box and select the company name field for field 2. You can hide the first field from view by selecting that option in this same dialog. The users click/tab into the field and see a list of company names when they select 1, the company ID number is entered into the field. Use the pop up menu format with this field and the user never even sees the ID.

                                 That works for fairly short lists of companies. Option 2 is more elaborate, but makes it easier to work with longer lists of names, yet still enters and ID number for the name selected:

                                 FileMaker 12 users: https://dl.dropbox.com/u/78737945/SimpleNameLookupDemo.fmp12
                                 Pre FileMaker 12 Users: https://dl.dropbox.com/u/78737945/SimpleNameLookupDemo.fp7