1 2 Previous Next 25 Replies Latest reply on Jan 27, 2012 2:12 AM by SEG-IT-Support

    HELP......hard to solve

    ras75

      I HAVE GOT THREE TABLES

      SHARES ORDERS CUSTOMER

      SHARE_id ORDER_id CUSTOMER_id

      SHARE DESCRIP SHARE_id

      CUSTOMER_id

      DATE

      QUANTITY

      PRICE

       

      Table SHARE is linked with ORDERS:SHARE_id

      Table ORDER is linked with CUSTOMERS:CUSTOMER_id

      One customer can do many order

      the order is not like an invoice with multi share

      in one order only one share each time

      Each share can have multi Orders

       

      My problem is that when i go to LAYOUT coustomer i need a portal where i have SHARE TABLE and in each row the total quantity of shares for that customer, and when i co to LAYOUT share i need a portal where i have SHARE CUSTOMER and in each row the total quantity of shares for each share in portfolio.

      i need also to check, when i am in LAYOUT ORDER, after had insert share_id and Customer_id a field where i had the position for that customer and for that share before and after the current order.

       

      At the moment i have got all the portals working but not for the quantity. I try muli attemps but sometimes i alway have total not mathcing. I meen or the total of the toal or the total for each customer without break for the share and something like this.

       

      Regards

        • 1. Re: HELP......hard to solve
          Stephen Huston

          I may still be confused about what you are doing, but my suggestion for simply counting related records is to use the Count Function in a calc within the main table to which the counted records are related.

           

          So if you have Customer with a primary Key of CustomerID and an Order table with a foreign key of Customer_ID_fKey, in Customers your calc would be:

           

          Count ( Order::Customer_ID_fKey )

           

          You know that field will be populated in all related records, and the relationship determines which records to count as related.

           

          HTH,

          Stephen Huston

          • 2. Re: HELP......hard to solve
            Mike_Mitchell

            Hello, ras75 (I'd address you by name, but I don't know what it is).

             

            Looking at your question, it appears you're designing a stock tracking database. Assuming that's the case, you have a desire to track the orders that are placed, and the positions before and after the sale. Perhaps rethinking the design a bit might help you.

             

            Instead of thinking of it in terms of Shares, Orders, and Customers, what if we add another table: Positions? This is what I'm thinking:

             

            Whenever a Customer puts in an Order, it creates a change of position, right? That customer has either added or removed a number of shares from his account. So, what if I have a table in which each row represents the combination of Customer and Share? You would hold the date, customer ID, share ID, and number of shares. In other words, as of X date, customer Y had N shares of stock Z.

             

            What does this do for you? Two things: It satisfies your requirement to know before and after positions, because all you have to do is look at the records before and after the order. And, it satisfies your need to know the current position on a share - it's the most recent record.

             

            Given that, you would just script the new order to create the new Positions record, adding or subtracting shares as appropriate.

             

            HTH

             

            Mike

            • 3. Re: HELP......hard to solve
              ras75

              This is what i need.

              in the customer layout a portal where i find in each row the share and the total quantity of that share

              in the share layout a portal where i find in each row the customer and the total quanty of of the share for that customer

               

               

              LAYOUT ORDER

              LIST VIEW

              CUSTOMER        SHARE        QTA

              JOHN               MSFT           100

              MARK               IBM             1,000

              LUIS                ORCL           1,000

              LUIS                IBM               500

              JOHN               MSFT            100

              JOHN               ORCL            500

              MARK               F                 200

              MARK               MSFT            100

               

              So if i go into the customer layout FORM view

              CUSTOMER JOHN

               

              PORTAL

              MSFT       200

              ORCL       500

              or

              CUSTOMER MARK

               

              PORTAL

              IBM          1,000

              F               200

              MSFT          100

               

              etc etc

               

              IN THE LAYOUT SHARE FORM view

               

              SHARE IBM

              PORTAL

              MARK 1,000

              LUIS  500         sorry

              OR

              SHARE MSFT

              PORTAL

              JOHN  200

              MARK  100

               

              OR

              SHARE IBM

              PORTAL

              MARK  1000

              LUIS     500

              • 4. Re: HELP......hard to solve
                comment

                ras75 wrote:


                when i go to LAYOUT coustomer i need a portal where i have SHARE TABLE and in each row the total quantity of shares for that customer,

                 

                This is not simple to do in a portal. Please state your version, so that we know what is possible.

                 

                OTOH, it is very simple to produce a report of the Orders table, summarized by customer and by share (or by share and by customer to  meet your other requirement).

                 

                 

                ras75 wrote:

                 

                i need also to check, when i am in LAYOUT ORDER, after had insert share_id and Customer_id a field where i had the position for that customer and for that share before and after the current order.

                 

                You can define a self-join relationship of the Orders table as:

                 

                Orders::CustomerID = Orders 2::CustomerID

                AND

                Orders::ShareID = Orders 2::ShareID

                AND

                Orders::Date > Orders 2::Date

                 

                Then you can calculate the position before the current order as =

                 

                Sum ( Orders 2::Quantity )
                
                • 5. Re: HELP......hard to solve
                  ras75

                  I have FM 11

                   

                  i try.

                   

                  i have got it on report but i need it in a portal so i have all over there

                  • 6. Re: HELP......hard to solve
                    ras75

                    how i can populate and update it?

                    • 7. Re: HELP......hard to solve
                      comment

                      You need to add a global field gCustomerID to the Shares table, and define another relationship between Shares and Orders as:

                       

                      Shares::ShareID = Orders 3::ShareID

                      AND

                      Shares::gCustomerID = Orders 3::CustomerID

                       

                      Also add a calculation field =

                       

                      Sum ( Orders 3::Quantity )
                      

                       

                      This is the field you need to place in the portal. In the Customer layout, you need a script triggered OnRecordLoad to set the Shares::gCustomerID field to the value of CustomerID of the current record.

                      • 8. Re: HELP......hard to solve
                        ras75

                        where i have to add Sum ( Orders 3::Quantity )?

                         

                        for the script triggers........some problems

                        • 9. Re: HELP......hard to solve
                          ras75

                          at the moment nothig working

                          i set the scrip trigger but in the global field FM put the same number for each record

                          • 10. Re: HELP......hard to solve
                            comment

                            ras75 wrote:

                             

                            where i have to add Sum ( Orders 3::Quantity )?

                             

                            In the Shares table.

                            • 11. Re: HELP......hard to solve
                              comment

                              ras75 wrote:

                               

                              in the global field FM put the same number for each record

                               

                              Correct, that's how global fields work.

                              • 12. Re: HELP......hard to solve
                                Mike_Mitchell

                                Use a script to create the Orders record - triggered through a button. As part of that script, update the number of shares on the corresponding Positions record.

                                • 13. Re: HELP......hard to solve
                                  comment

                                  Mike_Mitchell wrote:

                                   

                                  Whenever a Customer puts in an Order, it creates a change of position, right? That customer has either added or removed a number of shares from his account. So, what if I have a table in which each row represents the combination of Customer and Share?

                                   

                                  Then you have a table of redundant data.

                                  • 14. Re: HELP......hard to solve
                                    beverly

                                    time to fire it. LOL

                                    1 2 Previous Next