6 Replies Latest reply on Nov 21, 2013 6:38 PM by john.s

    A challenging Script…at least for me

    john.s

      I'm trying to develop my first FM database. The system will be used to manage a Photography Business with School Photography, Weddings, etc. I've hit a brick wall with a report I need to create a report the total number and dollars for each pkg ordered and summed by school. The input that comes back from the photo lab is causing me some grief. I’m sure this can be done but I’m having trouble figuring it out. I’m still in the trial stage of FMP and need to be sure this can be done before I make the investment for my business. Everything else I need to do I have a good handle on except this…and with about 10,000 line items to add up this is a biggie for me.

       

      The Input is a line item table that has one row per order with a school code, and four fields where the packages ordered and quantity for each package are stored. The problem I’m running into is any package can be in any one of the four Pkg fields with a quantity associated with that package. To further confuse the situation is each school can have a different price for each package…therefore I have a table with the school code and the pricing level for that school code along with the price for each package in the price level. The "np" in the table below indicates no purchase

       

      Here is what the tables will look like and while these tables will have additional information non of it is related to this particular challenge.

       

      OrderInput

       

      School CodeFk

      Student

      Pkg1

      Quantity1

      Pkg2

      Quantity2

      Pkg3

      Quantity3

      Pkg4

      Quantity4

      1

      Joe

      A

      1

      F

      2

      H

      1

       

       

      1

      Shannon

      C

      1

       

       

       

       

       

       

      2

      Tom

      A

      1

      G

      1

       

       

       

       

      1

      George

      D

      2

      K

      2

       

       

       

       

      1

      Ringo

      F

      1

      L

      1

      M

      1

      Q

      2

      2

      Sue

      B

      1

      C

      1

       

       

       

       

      2

      Deb

      A

      1

      K

      1

       

       

       

       

      1

      Joe

      G

      1

       

       

       

       

       

       

      5

      Tom

      np

       

       

       

       

       

       

       

      1

      John

      C

      2

      J

      3

      K

      1

      L

      1

      10

      Paul

      D

      2

       

       

       

       

       

       

      10

      Rowan

      A

      1

      L

      2

       

       

       

       

      1

      Scott

      np

       

       

       

       

       

       

       

      1

      Debbie

      G

      1

      I

      1

       

       

       

       

       

      ProductPricing

       

      PriceModelPk

      PkgA

      PkgB

      PkgC

      PkgD

      PkgE

      PkgF

      PkgG

      ...

      STANDARD

      $19.00

      $27.00

      $39.00

      $49.00

      $12.00

      $17.00

      $16.00

       

      SILVER

      $17.00

      $25.00

      $38.00

      $46.00

      $12.00

      $15.00

      $16.00

       

      BRONZE

      $15.00

      $23.00

      $37.00

      $45.00

      $12.00

      $13.00

      $15.00

       

      PLATINUM

      $14.00

      $21.00

      $35.00

      $42.00

      $12.00

      $12.00

      $15.00

       

       

      SchoolPricingLevel

       

      School CodePk

      SchoolName

      PriceModelFk

      1

      New Heights High

      STANDARD

      2

      Old Glory High

      BRONZE

      3

      etc

      SILVER

      4

       

      STANDARD

      5

       

      STANDARD

      6

       

      SILVER

      7

       

      BRONZE

      8

       

      PLATINUM

      9

       

      SILVER

      10

       

      STANDARD

      11

       

      BRONZE

      12

       

      BRONZE

      13

       

      PLATINUM

      14

       

      PLATINUM

       

      Any help will be GREATLY appreciated,

       

      John

       


        • 1. Re: A challenging Script…at least for me
          erolst

          john.s wrote:

           

          I’m still in the trial stage of FMP and need to be sure this can be done before I make the investment for my business.  Everything else I need to do I have a good handle on except this…and with about 10,000 line items to add up this is a biggie for me.

           

          Hi John,

           

          welcome to the world of FileMaker. Rest assured that FileMaker is more than up to this task.

           

          Your problem is less one of scripting, and more one proper relational structure. Fields like package2, price3 are a surefire indicator of problems looming ahead.

           

          Define your package line items as one line item per package, like in a regular order or invoice – one line per product. Then you can use the school's default pricing level to look up the price from the PackagePricing table.

           

          Here's a file that illustrates how this works.

          • 2. Re: A challenging Script…at least for me
            john.s

            Thanks Erolst,

               The problem I have is I don't create the line item order.  The orders are sent to a photo lab and a file like the one in my example is what I get back.  If they would send one record per package ordered this would be fairly simple, I think.    But that input file is what I have to work with...any suggestions on how to do it with this crazy input record?

             

            John

            • 3. Re: A challenging Script…at least for me
              erolst

              Well, then it seems we *are* in scripting country. You could import the file into a temporary table and write a script that generates individual line items with the data from the fields. Does each line relate to an already (in your system) existing order, or would these orders have to be created?

              • 4. Re: A challenging Script…at least for me
                john.s

                I was afraid you were going to say that...scripting that is.  What happens is I get a file from each school with Student Name, Student ID and some stuff I don't need.  I import that into what will end up being the lineorder table you see in my example.  Students place orders on photo day (piece of paper) and those are entered into the labs software.  The file from the school is sent to the Photo Lab and they append it with the P1 Q1 P2 Q2...data and an image number that was used to print the photos.  I was then going to import that file into the same table updating the fields (matching on Student ID).  Just to add another twist...if a student is not happy with their photo (must be because of what they were wearing) they have their photo taken again on a retake day.  Those orders go to the Photo Lab and the Lab sends another version of the schools file with only the records from the retake...the lab adds a 99 to the end of the csv filename so I can distinquish it from the original file.  That file gets imported into the same table (I'm thinking that should be a different table) and I match the Imagenumber field on the imput file with a retakeimagenumber in the table.  So the table has two fields for image numbers, original and retake.  Can you see why I in over my novice head?  I'll do it anyway you think is best but I do need to identify any students that have not had their photo taken because that means they do not have an ID card and the schools want to know that.  With the file I was using that was simple because there was only one record per student.

                 

                Thanks for your help,

                 

                John

                • 5. Re: A challenging Script…at least for me
                  erolst

                  John,

                   

                  no worry, this probably seems (and is!) a bit much for a novice, but there are lots of people here who will gladly help you, as long as you can clearly define your tasks(s).

                   

                  So actually, you receive a list of students from the schools, and enter them into our system. Basically, each student is one order. Everyone uses the same student IDs, so there is no ambiguity

                   

                  In this case, then each student/order record with at least one line item has had their photo taken. Should there be a retake, you could import the new .csv and add those line items, flagged as retake (or some such), or replace the existing line items. I'm not sure where the image number fits in – that doesn't appear in your outline of the input file above.

                   

                  Anyway, here's an updated version of the file, with a script that processes an input file of the structure outlined above and generates line item records – and new orders, but then from your description I think that those orders are already in the system: it's the student records. Maybe you could clarify the flow of data between the school, yourself and the lab …

                   

                  If you think this might be helpful, be sure to read the comments in the script. In case you need further assistance, don't hesitate to ask!

                   


                  • 6. Re: A challenging Script…at least for me
                    john.s

                    Thanks Eroist,

                     

                    I’ve been trying to understand this all evening and my head is about to explode…it is late here on the east coast so I need to sleep on it and try again tomorrow.

                     

                     

                     

                    John