2 Replies Latest reply on Jun 2, 2017 3:35 PM by Jason Wood

    Move Entry data to portal same layout screen and table

    sidetrack_ca

      I am confused on how to tackle this challenge and I was hoping to see if anyone had some advice on how they might handle this.

       

      Description:  I am putting together a solution for my sales people to enter their sales into an app that will calculate their commissions earned per sale.  Ultimately the sales manager and the finance department will be able to run reports on this information to determine commissions earned.  The first part of the program will be the layout where the sales person enters in information related to a particular sale.  When the sales person opens this layout to enter information about the sale, they first choose the month and year that the sale occured.  after choosing the year, a script will trigger to find all previous sales within that month and year and display the summary below in a scrollable window (I am hoping this can be a portal) The sales person enters information into the top part of the layout and when they click the "save" button the information is summarized on a line below.  Each sale they enter will show up as a new line.

       

      I have attached a pic of my layout with arrows that show which information I want to move to the summary area.

       

      My issue is that I can not for the life of me figure out how to accomplish this moving of data to the summary area.  If the information in the summary area was form a different table then I would use a portal but I can not see a way to use a portal to display information from the sale table that is setup for the layout. 

       

      I am using FM15 Pro Advanced

       

      I have two tables:

       

      Sales.Transaction - This table contains information that is specific to each transaction that a salesperson enters and contains fields like:

           __Sales.Transaction.pk

           Customer.Name (we call if practice name because they are doctors)

           CW (a.k.a. Service Ticket #)

           Quote

           Sale.Date

           Category

           Sub.Category

          

      Products - The products table contains information about the specific products with fields like:

           __Sales.Transaction.fk    

           Product.Name

           Product.Cost

           Product.Sell Price

       

      Thank you in advance for taking the time to read this and assist if you can.

       

      Rob

       

      Screen Shot 2017-06-02 at 2.11.08 PM.png

        • 1. Re: Move Entry data to portal same layout screen and table
          Jaymo

          You can use a filtered relationship to accomplish this task.

           

          1) Create a calculation Called MonthYear to extract the month and year:

           

          Month(Sale.Date) & "" & Year(Sale.Date)

           

          2) Create a self-join relationship from the table occurrence attached to the layout show in the screen shot to another occurrence of from the same source table. The relationship should have two matches:

           

          MonthYear = MonthYear

           

          and

           

          __Sales.Transaction.pk__Sales.Transaction.pk

           

          3) Create a portal based on this relationship and you will have the same you desire. Sort the portal if you like to order them chronologically.

          • 2. Re: Move Entry data to portal same layout screen and table
            Jason Wood

            On the layout you've shown us, which is based on the Sales table, you want to see other sales from the same year and month in a portal?

             

            That's easy.

             

            Go your relationship graph, add a new table occurrence for Sales. Call it Sales|SameMonth or something like that.

             

            connect the year and month fields.

             

            Now point your portal at Sales|SameMonth