AnsweredAssumed Answered

A challenging Script…at least for me

Question asked by john.s on Nov 21, 2013
Latest reply on Nov 21, 2013 by 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

 


Outcomes