9 Replies Latest reply on Nov 2, 2016 2:10 AM by zlu

    Issue with DB structure

    zlu

      Hi all,

       

      Well, I'll try to be as brief as possible. It's my first DB so I'm trying to be concise.

      I'm actually looking for help as I don't even know what keywords to use in order to search for the info I need by myself.

       

      Here is my problem. One part of my DB has a structure that looks like this:

       

      Orders--<Products_Ordered>--Products--<Products_in_Delivery>--Delivery>--Orders_2

       

      From my Delivery Table, I can set which products are being delivered to a customer, and link that delivery to the Order table through a second occurence of that table (we do this because sometimes one order can not get delivered at once, so we have to create several delivery entries; I'm still a newbie at FM, but I think that I got this part right).

       

      Now, the problem is when I want to print an delivery slip.

      I can get all the info about that specific delivery, of course (name of products being delivered, quantity delivered).

      I can also display the order number (through my relationship with the Orders_2).

      But I can't display the number of each products ordered at first by the customer, and can't figure out how to indicate how many products are yet to be delivered.

       

      I know that all this info is buried in my "Products_Ordered" join table, but how can I get it?

       

      I've been struggling for hours now and really thank you all in advance for your advices...

       

      zlu

        • 1. Re: Issue with DB structure
          zlu

          By the way, my report layout is based on the <Products_In_Delivery> join table.

          • 2. Re: Issue with DB structure
            erolst

            • in ProductsInDelivery, add a calc field that references the orderID in Delivery

            • add a new table occurrence of ProductsOrdered and relate it to ProductsInDelivery via the orderID and the productID; now every product in delivery can see “its” order details

            1 of 1 people found this helpful
            • 3. Re: Issue with DB structure
              zlu

              Wow, thanks a lot for the input Erolst!

               

              I think a got the first part right.

               

              Now I have a ProductsInDelivery :: OrderIDfk_c calc field that references the OrderIDfk previously stored in my Delivery table, right?

               

              I don't really understand the second part of your suggestion though.

               

              I added the new occurence of the ProductsOrdedered table.

              Now, how exactly should I relate it to ProductsInDelivery?

              Should it look like this:

               

              ProductsOrdered_2 :: ProductsID ------- ProductsInDelivery :: OrderIDfk_c

               

              Is this what you meant?

               

              Sorry for the hassle

              • 4. Re: Issue with DB structure
                erolst

                zlu wrote:

                ProductsOrdered_2 :: ProductsID ------- ProductsInDelivery :: OrderIDfk_c

                 

                That doesn't really make sense, right?

                 

                You should relate the same (type of) keys to each other:

                 

                ProductsInDelivery::OrderIDfk_c = ProductsOrdered_2::OrderID

                ProductsInDelivery::ProductsID = ProductsOrdered_2::ProductsID

                 

                That means your relationship has two lines (predicates); set the first pair, then click add and connect the second set.

                 

                With only one predicate, you would see all order lines of either the same order the delivery is based on, or of the same product (regardless of Order) – but you only want the single order line that is the same product in the delivery's order.

                1 of 1 people found this helpful
                • 5. Re: Issue with DB structure
                  zlu

                  Well, I posted that comment, then read it again and though "Boy, this sounds stupid".

                   

                  Still, you were nice enough to answer

                  Thanks a lot, this solved everything, and I learned a lot in the meantime.

                   

                  Thanks again!

                  • 6. Re: Issue with DB structure
                    zlu

                    So erolst, everything works fine regarding the question I originally asked.

                     

                    But, if by any change you're still around sooner or later, I have one last question (the last one before actually geting this DB done...).

                     

                    I did manage to format my Print invoices.

                    The data I was looking for is now displayed, thanks to your help.

                    It looks like this:

                     

                    • List of products being delivered (let's say one "Imaginary Product")
                    • Date of delivery (let's say "Monday 1st")
                    • Quantity of "imaginary products" delivered (let's say "5")
                    • Quantity of imaginary products originally ordered by the customer ("10")
                    • Quantity left to be delivered later

                     

                    This is where things doesn't work. On this delivery slip, I'm getting "5" as a result for this last field.

                    10 – 5 = 5, so up to that point it's indeed fine.

                     

                    But I actually created another delivery record for this same order, but on a later day ("Tuesday 2nd").

                    And on that one, I have 2 "Imaginary products" being delivered to the customer.

                     

                    I'm sure you've guessed where I'm messing up, but instead of getting

                         "Quantity left to be delivered = 3"

                              [Qtty of products ordered "10"] – ([Monday 1st delivery Qtty "5"] + [Tuesday 2nd Delivery qtty "2"])

                         I'm getting 7.

                     

                    Any idea to get this calculation right?

                     

                     

                    • 7. Re: Issue with DB structure
                      erolst

                      zlu wrote:

                       

                      Any idea to get this calculation right?

                      You didn't show the actual calculation with the actual field references.

                       

                      But the problem here is that different deliveries should have different results; because if you ever print slip 1 again, you want to to see the status as it was at that time (i.e. 5), not the status taking into account all later deliveries (which eventually, of course, should be 0).

                       

                      So the quantity left for any given product is the qty order minus the sum of this delivery and all earlier ones. One could solve via a relationship and a calc field, but the problem here is that the date is an attribute of the delivery, not the line item. Also, this kind of unstored calc field could be a real performance breaker.

                       

                      How about using a stored field and a field trigger with a Set Field[] along these lines?

                       

                      Let ( [

                      delivered =

                         ExecuteSQL ( "

                           SELECT SUM ( qty )

                           FROM ProductsInDelivery pid

                           JOIN Delivery d ON d.productID = pid.productID

                           WHERE

                             pid.deliveryID = ? AND

                             pid.productID = ? AND

                             d.dateDelivery <= ?

                           " ; "" ; "" ;

                           ProductsInDelivery::deliveryID ; ProductsInDelivery::productID ; Delivery::dateDelivery

                          ) ;

                      ordered = ProductsOrdered_2::OrderID // now would be a good time to give your TOs better names … ;-)

                      ] ;

                      ordered - delivered // written from scratch – absolutely untested! No SQL support here! ;-)

                      // careful when dealing with an already filled-in field – this sort of stock/qty management can be tricky

                      )

                       

                      You could use this also to do a plausibility check on any entries, like “delivered must not exceed ordered” etc.

                       

                      Of course, if you really only care about the current slip, you can create a calc field in ProductOrdered as

                       

                      Sum ( ProductsInDelivery::qty )

                       

                      and evaluate this from the context of the new TO (so you use that relationship in reverse). Now display that related calc field on ProductsInDelivery. This is the aforementioned field that eventually will read 0.

                      1 of 1 people found this helpful
                      • 8. Re: Issue with DB structure
                        zlu

                        Well, your last suggestion was within my grasp, and is working fine for my needs right now.

                        Thanks a lot, seriously

                        • 9. Re: Issue with DB structure
                          zlu

                          Well well.

                          I thought everything went fine.

                           

                          I'm sorry to dig this out of the archives... but it turns out I failed implementing that last suggestion from erolst.

                          Of course, if you really only care about the current slip, you can create a calc field in ProductOrdered as

                           

                          Sum ( ProductsInDelivery::qty )

                           

                          and evaluate this from the context of the new TO (so you use that relationship in reverse). Now display that related calc field on ProductsInDelivery. This is the aforementioned field that eventually will read 0.

                          I thought that it was working, but I didn't test it correctly.

                          So I'm still left with this last question : could s.o. give me maybe a little more details on how I can make my delivery slip display the correct amount of products left for future delivery?

                           

                          Thanks a lot in advance, I'm kind of desperate now...