1 2 Previous Next 27 Replies Latest reply on Aug 6, 2009 7:07 AM by comment_1

    Portals and related fields

    LarsBC

      Title

      Portals and related fields

      Post

      Hello,

       

      I've got quite a basic problem (I think) that I hope someone can help with.

       

      I've got a database consisting of a table of Stock and a table of Orders. These tables are related so that when you create a new Orders record, you select an item from a dropdown box based on the Stock table, and several other fields related to the selected item are then displayed from the Stock table. Among others, the Orders table has its own field called Order Status, which can be Pending, Backorder or Delivered.

       

      My problem has arisen because I wanted to create a layout where I could display a list of the different types of orders depending on their Order Status. The only way I could work out how to do this was to create a new table with just one field, Order Status, relate that field to the Orders table, and then create a portal that displayed the info from the Orders table that way. I was hoping to do this via a layout, but there didn't seem to be any way to get the layout to only display records based on data in a specific field (if anyone knows how to set this up, I'd be grateful).

       

      My problem with the portal approach is that the portal in the third table displays the data in the tables that are "native" to the Orders table just fine, but the fields in the Orders table that are populated based on data in the Stock table just come up blank in the portal. Why? I understand that the third table and the Stock table aren't directly related, but I don't see how they can be since they have no fields in common, only the ones in the portal, which I can't base a relationship on. Is a portal not able to display data that is once removed, so to speak?

       

      I hope the explanation above makes sense. Thanks for reading.

       

      Lars

       

       

       

        • 1. Re: Portals and related fields
          philmodjunk
            

          Don't use a portal, use a list/report style layout. Now you can use a find request to find your records and sub-summary parts with a record sort to group your orders by status.

           

          Go into layout mode and Select New Layout

          In the layout setup wizard that pops up, choose List/Report as your layout type.

          Choose Report with grouped data

          Select the fields you want for your report (you can re-arrange and re-size these as needed later.)

          Choose Order Status as your report category.

          Set up how you want these records sorted, just make sure that Order Status is part of the sort order.

          Select your theme

          Select any additional stuff you want at the top(header) or bottom (footer) of each report page.

          Choose "do not create a script"

          Choose to view the report in layout mode so that you can adjust field position, size and add any layout text you want.

          Enter browse mode to see the result

           

          You can now use find to select specific records--just make sure that you sort the records by order status in order for the sub-summary part to be visible. (In pre FMP 10 versions, this part is only visible in preview mode or when printed).

          • 2. Re: Portals and related fields
            comment_1
              

            LarsBC wrote:

            Is a portal not able to display data that is once removed, so to speak?


            If your relationships are set as:

             

            Viewer -< Orders >- Stock

             

            and you have a portal on a layout of Viewer showing records from Orders, you should have no problems placing fields from Stock in the portal and have them display the relevant data for each portal row.


            • 3. Re: Portals and related fields
              LarsBC
                

              Thanks for your replies. Man, this is an insanely busy forum.

               

              To the first reply, the layout suggestion is great, and I tried to follow your instructions. However, I ended up with a lovely layout that displayed the data from my table in a very similar way to the portal I made earlier, except without the Order Status field anywhere to be found. I definitely selected the Order Status field as one of the fields to be used, and also selected it as the report category, but the layout doesn't show order status and doesn't sort the entries in any way.

               

              Furthermore, the layout has the same problem as the earlier portal - it displays the data from the Orders table, but not the data pulled into the Orders table from the related Stock table (except for one solitary bit of text in one field - that one shows up fine, even though it should be identical to all the other fields that don't display).

               

              To the second reply, I don't understand what you mean by "Viewer -< Orders >- Stock". I am not a database expert or IT professional, so you may have to explain things to me as you would to a 6 year old. :) The relationships between all my tables are set to "=", as I figure that I only want data to show that match my criteria exactly...

               

              Can I attach screenshots here, if that might help?

               

              Thanks again,

               

              Lars

               

              P.S. 

              I also have a follow-up question - I've been asked to track numbers and prices as well as just order status, so I made a "Number" field (of type "number") and a "Total Price" field (of the type "calculation") in my Orders table. I tried to set up the calculation field to simply be (total price = item price * number), but although it looked simple enough to do, when I try to perform the calculation by entering a number in the number field, the total price is always 0. My guess is that this is because the calculation is hampered by the same problem as above, that the "item price" field data is pulled from the Stock table rather than being native to the Orders table and thus the calculation can't see it.

               

              Edit P.S.:

              OK, I sorted out the calculation problem by telling the calculation formula to use the data directly from the Stock table rather than from the related field in the Orders table.

               

              • 4. Re: Portals and related fields
                philmodjunk
                  

                "...without the Order Status field anywhere to be found. I definitely selected the Order Status field as one of the fields to be used, and also selected it as the report category, but the layout doesn't show order status and doesn't sort the entries in any way."

                You must first sort your records by order status. Pull down the records menu, select sort and specify the Order Status field.

                 

                "Furthermore, the layout has the same problem as the earlier portal - it displays the data from the Orders table, but not the data pulled into the Orders table from the related Stock table (except for one solitary bit of text in one field - that one shows up fine, even though it should be identical to all the other fields that don't display)."

                You can add any field from a related table that you want and put it anywhere on the layout that works for you. Simply enter layout mode and use the field tool to drag and drop additional fields on your layout. The sub summary part that will appear once you sort your records is a likely place to add these.

                • 5. Re: Portals and related fields
                  comment_1
                    

                  LarsBC wrote:

                  I don't understand what you mean by "Viewer -< Orders >- Stock"


                  It's an approximation of what you should see in the relationship graph. It means that one viewer record is related to many orders, and one stock is related to many orders - AND that these relationships are daisy-chained within the same group, so that Viewer is indirectly related to Stock.


                  • 6. Re: Portals and related fields
                    LarsBC
                      

                    Thanks again. The layout seems to work after sorting.

                     

                    Regarding the portal thing though, since I can't work out what is wrong, here are some screenshots of my Stock table, my Viewer with portal and the relationships between my tables. Maybe you can see what is wrong and why the fields in the portal are blank that shouldn't be.

                     

                     

                    StockPortalRelationships

                    • 7. Re: Portals and related fields
                      comment_1
                         Why don't you post a file - it will be much easier to spot the issue. 
                      • 8. Re: Portals and related fields
                        LarsBC
                           Good call. How do I attach a file to my message?
                        • 9. Re: Portals and related fields
                          comment_1
                             You can't - do the same thing as you did with the images.
                          • 10. Re: Portals and related fields
                            LarsBC
                              

                            I just used the "insert image" option and photobucket, I don't think that will work for a database file sadly. :(

                             

                            If you give me an email address I can just email it. I take it you are filemaker staff?

                            • 11. Re: Portals and related fields
                              comment_1
                                

                              I meant upload the file somewhere, and post a link to it here.

                               

                               

                              I'm just another user here - same as you.

                              • 12. Re: Portals and related fields
                                LarsBC
                                  

                                Cool, thanks for being so helpful and not even getting paid for it! :)

                                 

                                I'm not sure where I could find to host the file, but I'll see what I can do.

                                 

                                 

                                • 13. Re: Portals and related fields
                                  comment_1
                                     Google: "free file hosting"
                                  1 2 Previous Next