Complex Find Help needed!

[redcalx]

Ok, heres a somewhat complicated (as far as I see it) situation I need a little help with if anyone has a few minutes.

I have created an invoice database for a friend of mine that has 7 product fields on it. Basically one line across the invoice looks like this:

BRAND (brand of product being sold) - QTY (quantity of product) - PRODUCT (product description) - UNIT PRICE (unit price) - TOTAL PRICE (QTY x UNIT PRICE)

The problem is that basically each of those above fields I have 7 of them (So Product 1, Product 2, Product 3, etc) but now they want to be able to print a report of all "x" brand name items and their total price sold.

I have no idea how to create a report for this, or do a find since I can't put all Product1, Product 2, Product 3 etc rields in the report layout)...

Is there an easier way for me to do this? and How would I create a report that would display this kind of information found?

I've included a graphic if it helps anyone...

Oh, and just for fun... If there is a way to do what I want... can I format the field of the description with a background color based on the brand?

Thanks for all replies in advance...

[Ugo DI LUCA]

And which version are you running ? FM 2.1 ?

If not, download a FM Templates at http://www.filemaker.com and you would profit from relationships...

[redcalx]

I'm running 5.5

I dont think you understand my question (or you underestimate how new i am at this :

I need to create a report that basically pulls from 7 fields to report in one if the brand matches... does that make sense?

[-Queue-]

I would create a file that holds the individual information for each of the items selected in one record. The general idea would be that one portal to the related file would hold all the information you've separated into multiple fields. See the attachment for an example.

For your formatting question, you'll need a global repeating container field holding each color selection, and a calculation container field that tests the brand and returns the desired repetition, e.g. Case( Brand = "A", GetRepetition( gContainer, 1 ), Brand = "B", GetRepetition( gContainer, 2 ), ... )

If you have the brands in a value list and an unstored calculation field VLBrand equal to ValueListItems( Status(CurrentFileName), "valuelistname" ), then you can retrieve the color dynamically, as GetRepetition( gContainer, PatternCount( Left( BrandVL, Position( "

inv.zip

[redcalx]

Thanks Queue for the help, but did you look at the image i attached?

Right now, there is 7 spots on the invoice to enter information, not just one... otherwise it would be really easy...

If you could, take a look at the image I attached and let me know what I can do in the current situation that would be awesome!

[Ugo DI LUCA]

redcalx said:

I'm running 5.5

I dont think you understand my question (or you underestimate how new i am at this

I need to create a report that basically pulls from 7 fields to report in one if the brand matches... does that make sense?

Or I overestimate how new you are at this.

Where Queue is driving you is where I was also "carrying" you. Basically, having records displayed as you presented in your attachement was the old FM Fashion. I currently still run 2 apps with FM2.1 which is why I referred to it.

Let say, that when FM evolves with relationship, this kind of display still would exist but the structure needed for it has changed. Involving relationships helps keeping accurate information even if the situation changes and you'd finally need 8 fields rather than 7.

Now, I don't get your point about having only one remaining line if all 7 where from the same Brand.

Really, if you're that newbie you said, just spend some time on the Forum or download this Ready-made template I suggested.

[-Queue-]

Yes, I saw the image. You can format the portal to display seven lines. When you click into the row below the last one with information, it will automatically create a new record in the lines file, and you can enter (or select, if you format the field as a menu or list with a value list containing all possibilities, as Product is in the example I attached) the quantity, brand, product, etc. for each of the seven items. I would take Ugo's advice and do some searches for invoice or download some template files.

[redcalx]

Ok... almost there.

Your last past was helpful, but you still arent getting what I'm trying to do. On the invoices themselves, theres seven possible slots to enter different products...

In my report, I want to display ALL of the descriptions and prices from any products that are "x" brand from ALL invoices.

Each invoice might contain 3 purchases of product "y" and 2 of "product x", but in my search, Say I want to locate all the purchases of ONLY "product x" for a month, and have it look like this

BRAND DESCRIPTION PRICE

Y Table $550

Y Chairs $220

Y Bookshelf $700

In order to find this information however, I need the report to look at all seven available slots on each invoice but only report it in a list like what I just wrote above...

I'm sorry if I'm being confusing... I'm trying my best to learn filemaker... I searched for invoices but i didnt find much...

[Ugo DI LUCA]

Now move your find from "Invoices" to "Filtering Portal", because that's what you're about to do.

Litterally, filter those invoices where the Brand Item is X.

There are surely a lot of posts with attachments here about filtering portals.

[-Queue-]

And each line in the portal equals a new record in the lines file. Seven lines equals seven related records, each related to the invoice number. You do your find in the lines file, setting the product field to "x", "y", or whatever you desire and any additional criteria, and design a subsummary report to display the found information once you've sorted the records by brand and description, for example. Do a search for 'subsummary' on the forums, read FileMaker's help information, download some sample reports, and learn what you need to make it work.

What both Ugo and I have been telling you are possible solutions to your problem.

[redcalx]

THANK YOU... I just figured it out. I'm in a huge debt to both of you. Thank you very much! I will use the portal and line file...

THANK YOU THANK YOU!!!!