I need to setup relationship where I can add up multiple number of units to give packages...and multiple packages to boxes all for a given year 

 

for eg.

 

 Years      Units                      Packages                             Boxes

  2013      x11 =2                        x= 5                                     17

  2013      x12 =3

  2013      y11=6

  2013      y12 =6                       y=12

 

 2014       x11 =2                        x=5                                         15

  2014      x12 =4

  2014      y11=5                         y=10

  2014      y12 =5

           

 

 

I am not sure how to set up such complex relationships and 'sum' calculation. any suggestions will be appreciated !!    

assuming that you have the "x" and "y" split off and in their own fields in that table then you do not need relationships at all.  Just create summary fields for the qty and sort by year and by whatever field holds the x and y

 

You can also use ExecuteSQL() to generate the results without the summary fields.  Or use a looping script to count, ,,,

Hello Wim, thanks for reply. But x and y are in same table...not sure if you meant that...in short data is like this 

 

year      unitid    unit#    packageid   package#    boxesid    boxes#

2013      x11        2                x                 5               X            17

2013      x12       3

2013      y11       6

2013      y12      6                  y                  12

 

2014       x11      2                 x                    5                   15

2014      x12      4

2014      y11       5                 y                   10

2014      y12      5

             

there is a set relationship between x11/x12 to ->x    and   x/y to X ....

 

Not sure if summary field will help in this scenario...i have tried SQL but setting up multiple loops has been complicated :'(

What Wim meant is that your goal can be achieved by using a straightforward summary report if you have stored separately (or can isolate) the x/y part.

 

See the attached example.

UnitsPackagesBoxes_eos.fmp12.zip

this seemed to work...thank you eos !