A range of data

[ejpvi]

I think I know how to do this using loops, but I am wondering if there is perhaps a function to simplify this.

Lets say I have a table, that has a "Item" Field,"Book key" Field, and "Cost" Field.

The books are monthly catalogs, that have their own cost based on the item.

The books are sequentially stored as a number.

So it is safe to assume book 1 is first...then the following book is 2... etc...

The user wants to enter a Starting book, and an Ending book... then basically have it perform calculations to the range of books, including the ones in between the start and end.

I have a feeling I will need to perform some sort of find based on the item and then the book range... then perform the calculations with those results.

Go to the next item... repeat...

I was hoping there may be a simpler function or something...

Any ideas.... if this doesn't make sense...let me know.

To simplify this... just pretend I am trying to sum the costs of each item based on the book range.

Example of data in table:

Item: Book: Cost:

1 1 $5.00

1 2 $7.00

1 3 $8.00

2 1 $2.00

2 2 $8.00

2 3 $3.99

So if they chose start book 1... end book 3..

it would sum the costs together for those items...

I hope that helps.

Edited November 17, 2009 by Guest

[LaRetta]

Create a sTotalCost field (type summary which is total of Cost).

You can then either let the user search the book field as 3...6 or provide a global field (text) for your User to enter the range and perform the find. The sTotalCost will provide the total cost of the books in the found set. :wink2:

[LaRetta]

To simplify this... just pretend I am trying to sum the costs of each item based on the book range.

I might have misunderstood. So you want Item #1, and a sum of the cost of all books in the range, then Item #2 and its sum etc?

For this, you would want a columnar list layout grouped by leading part Item #. Place this same summary field in the leading part next to your Item # then sort the report by Item #. If you don't care if it is grouped by Item Number, then the sTotalCost will produce the correct result.

Edited November 17, 2009 by Guest

[ejpvi]

yes, I need it grouped by item first... then it sums the cost based on the range of books....

I am wondering how to do this using calculations though.

I will be creating calculated fields that have different equations. at a point in the equation I will probably need to plug in (Book 1 Cost + Book 2 Cost + book 3 Cost) which is basically a range (Starting book Cost......Ending Book Cost)

These will be for each item.

[LaRetta]

I am wondering how to do this using calculations though.

If you need this as calculation in Browse mode then you would use globals to hold gNumStart and gNumEnd and maybe even gItem# (see attached). This allows total flexability to the User. The field in blue is a summary from the self join (based upon the globals entered).

I will probably need to plug in (Book 1 Cost + Book 2 Cost + book 3 Cost) which is basically a range

I still have no idea why. Finds and relationships both can handle ranges just fine. It would help to know how the Users will be viewing/working with this data and why you think you'll need to 'plug in' an addition formula. :smile2:

books.zip

[Søren Dyhr]

Go to the next item... repeat...

:

--sd

[LaRetta]

Hey Søren, if that's your explanation of ejpvi's explanation of why calculations on a range would be needed, it is even less clear to me. I realize that phrase was said but it keeps coming back to summing the group; otherwise, repeat what? If you wish to provide ejpvi with an answer on something I don't see, that would be great and much appreciated! But I can't provide answers without details and I still don't see enough detail to make that determination.

Otherwise I'll have to assume you are foolin' with me and that'd be great too! :wink2:

Edited November 18, 2009 by Guest

[Søren Dyhr]

But I can't provide answers without details and I still don't see enough detail

Neither can I as usual ... but posting this under "calculation engine" and not "finding and searching" nearly reveals a spreadsheet'ish metaphor, where is the talk about records in plural where summaries are obivous? Is the notion "table" a database table or a grid'ish layout matter?

...and the use of an emoticon, suggest a sort of pulling ones legs for not being on exactly the same page as...

--sd

[ejpvi]

Ok, so far that seems like a perfect example of what I needed... but what if I wanted to do something other than summing it together... is it possible to utilize a Summary in that capacity... say multiplication?

[LaRetta]

is it possible to utilize a Summary in that capacity... say multiplication?

I'm sorry but I do not know; my mind isn't that broad without specifics and a vision to work with. Can you just provide a demo file getting to the specifics of what you want? I don't even know what you want to multiply or why, and I left my ouija board at home this morning.

With something to work with, I'll give it a good run; I would truly like to help but you aren't making it easy and I'm getting a bit tired. Others are welcome to step in as well.

Edited November 18, 2009 by Guest

[ejpvi]

Sorry for my lack of details... I am going to work with some of your examples... perhaps I can modify it to show you what I am trying to do... Thanks for your help.. take a break, I don't want to ruin your day :

[LaRetta]

Ya can't ruin my day ... we're now heading for the office Thanksgiving lunch!! Not to worry ... when you come up with some specifics, someone here (as you know) will be happy to assist. Some people are good working abstract; I'm just not one of them. Regardless, I'll give it a try if I can.

TURKEY TIME!!