9 Replies Latest reply on Apr 21, 2014 9:00 AM by philmodjunk

    Missing and incorrect data displaying in portal

    SamRich

      Title

      Missing and incorrect data displaying in portal

      Post

           I am getting incorrect data displaying in a portal. 

           I have a form layout called customer orders and payments. The main part of the layout displays the Order form. Then there are 2 portals, one for payments and one for order details. In order details the fields are ref numbers; date; (a few others) product; price; quantity; total. All display fine apart from total. The total displays fine in the Order details layout, just not in this one. 

           The problem began a few weeks back, when only the data in the top row of the portal displayed - the data in the other rows for the Total field remained blank. 

           Now, not only are the data for other records blank, but the data showing is actually incorrect, and not the same as in the Order Details layout.

           In the attached image you can see how there is only data in the top row of the portal for the Total field. The data is also incorrect - it should say 450 x 50 = 22,500 - instead it says 24,000.

           I'm completely mystified as to what's going on! Any ideas?

      fmp_portal_issue.jpg

        • 1. Re: Missing and incorrect data displaying in portal
          philmodjunk

               We have to start with the basics just to be sure:

               In what table is Total defined?

               What kind of field is it? Is it a calculation field or a number field with an auto-enter calculation?

               What is the calculation expression used to compute that total?

          • 2. Re: Missing and incorrect data displaying in portal
            SamRich

                 Hi Phil

                 Total is defined in the Order Details table.

                 It is a number field with an auto-enter calculation.

                 The calculation is Price * Quantity. (Both fields in the same table). 

                 I should add that this field worked fine for months and only recently behaved strangely. I'm not clear what I did to confuse it. First there was the missing data, and now what appears to be incorrect data.

                  

                 Sam


                  

                  

            • 3. Re: Missing and incorrect data displaying in portal
              philmodjunk

                   And is the "do not replace existing value..." check box selected?

                   is Price * Quantity the exact expression you'd get if you pasted the expression from FileMaker to here?

                   Have you recently changed any part of this calculation?

                   Examine the layout while in layout mode. Make sure that Total has been selected from exactly the same table occurrence as that specified in Portal Setup|Show Related Records From. If it is not, change this so that it is from the same table occurrence.

                   As a fix, you might change the field to type calculation. This will keep the same calculation expression but force an update of the calculated value. You can then experiment with changing it back into an auto-entered calculation.

                   That only fixes the current values, it doesn't explain why it is wrong in the first place.

              • 4. Re: Missing and incorrect data displaying in portal
                SamRich

                Phil

                      
                     No - do not replace existing value is not checked. 
                      
                     Yes, I copied and pasted the expression.
                      
                     I don't think I changed the calculation recently. When I look at an earlier saved version of the database it works fine, and I can't see any differences between them. Is it possible the database is corrupted?
                      
                     Layout mode - ok I examined it and actually the set up is more complicated than I initially explained:
                     The main table being used is the Contacts table.
                     Within contacts table the totals fields are from different tables. 
                     The portal is for the Orders table. Some of the fields are from Order details (like the fields in the calculation) and some of them (like the date and paid status) are from Orders 2 (a second instance of Orders table). 
                     I tried changing this around to see if it improved things, but it didn't: 
                     If I change the portal to reference the Order Details table, it works just as well, but the problem with the Total field remains. 
                     If I change the portal to reference Orders 2 - it shows the same reference numbers in the first two fields of the portal for each item.
                     If I change the fields to reference Orders instead of Orders 2, it seems to work just as well. 
                     This is probably TMI - but I'm attaching an image of the relationships in the database. 
                      
                     Calculation - I changed the field to a calculation, and again the data showed up correctly in the table, but not in the portal. I changed it back to an auto-calculation, and the problem remained. 
                      
                     Thanks in advance.
                      
                     Sam
                      
                • 5. Re: Missing and incorrect data displaying in portal
                  philmodjunk

                       Sorry, but there are quite a number of missing details in your last post. Are not total, price and quantity from Order details? But the portal is to Orders?

                       That won't work. What you should get in that case is values from the first related record in Order Details, which will not show a combined total based on all related records in Order Details.

                       If you want the total value of the entire set of related records in order details, you need to refer to either a summary field defined in Order Details or a calculation field in Orders that uses the sum function to calculate the same total from the set of Order Details records linked to each Order record shown in the portal.

                       But your original screen shot seems to list only a single product for each order. So I remain confused as to what you actually have set up here. Are you sure that this portal isn't a portal to Order Details?

                       

                  Some of the fields are from Order details (like the fields in the calculation) and some of them (like the date and paid status) are from Orders 2 (a second instance of Orders table).

                       That definitely adds to the confusion here--especially since you do not identify which fields reference which table occurrence!

                  • 6. Re: Missing and incorrect data displaying in portal
                    SamRich

                         Phil 

                         Sorry for not explaining things better.

                         All the fields in the portal (including quantity, price and total) are from Order Details, only date and paid status are from Orders 2. 

                         Yes the portal currently is set to Orders. When I changed it to Order Details, I found it worked the same, but also the problems were the same. 

                         I am not looking for combined totals, just the total of quantity x price in each Order Details record. 


                         Thanks again.

                         Sam

                    • 7. Re: Missing and incorrect data displaying in portal
                      philmodjunk

                           I don't think I can address the possible issues with your total field until your basic layout design makes sense to me.

                           What do you want one row of this portal to represent? What I see looks like you want to list all items from all orders for a given contact record. If each item purchased is a separate record in order details--the typical set up for an ordering or invoicing system, then it makes no sense to base the portal on Orders. Even basing it on Order Details is somewhat problematic as you appear to want to combine data about the order as a whole with specific data about each purchased item. Either you base the portal on Orders and only see data for the first Order Details record or you base the portal on Order Details, get each item purchased to list in your portal, but now the date and paid status fields repeat over and over on each row.

                           But I am making some assumptions here that may not be correct. Does each order list multiple items--each a record in order details? Or does each order consist of only one item type to each order?

                           Final note: If this is FileMaker 12 or newer and if the only row that shows data for Total is the first row in the portal and this is the case for any record you select in Contacts, enter layout mode, drag the total field out of the portal, release the mouse button and then drag it back into the portal row. Move the portal a pixel or two (ctrl-z or undo will pop it back into its original position) and make sure that all the fields in the portal row move when the portal is moved. This addresses why the data only appears in row 1 of the portal but not why it would compute the wrong value.

                      • 8. Re: Missing and incorrect data displaying in portal
                        SamRich

                             Phil

                             Thanks - once I pulled the field out of the portal and put it back in, all the data showed in each row of the portal, and the sums were correct as well.

                             You're right, this layout could give me some problems in the future. So far I am doing one item per order and this seems like a convenient fix for now, but I'll have to change this as the product range expands. Any suggestions, please let me know. 

                              

                             Sam

                        • 9. Re: Missing and incorrect data displaying in portal
                          philmodjunk

                               To make a suggestion would require that you decide how you want this portal to work for you. Currently, you seem to want it to do two different and slightly incompatible things. One option is to use two portals, one to orders and one to order details--such as a pair of "master-Detail" portals where you'd click to select an order in the first portal to perform a script that updates the second portal to list the order details for that one order. See this thread for details on how to set it up: Need layout solution for nested portals...

                               But that is just one of many options that you might explore.