Portal and Report Issue

[Brewser]

Hello,

I hope I can explain this well. I have created a small test inventory database to track the sales of certain products.

I have 2-tables one is a list of all my products and each record has an auto-index number. The second table is only my sold transaction info (QTY, Sold Date, & Sold Price), this table also has it's own auto-index number.

I used the field "product ID" in both tables to join and form the relationship.

The 'Product ID' in the products table is the autonumber and the 'Products ID' in the 'sold items' table is the related field.

On my main layout of products I created a portal to the 'sold items' table and have a list of 5 rows with a scroll bar.

Everything works great until I created a report. I want the report to show every item that was sold and the QTY, Sold Date, Sold Price. This could (in most cases) show the same product more than once with a different sold date and price. When I run the report script it will only show the product once and the first sorted record from the 'sold items' table. It will show all the products sold, but only list each one once, even though I may have sold the same item 5 times.

I have also kept track of the QTY and Sale price in the subtotals and it gives me a correct amount. example if I sold only 3 products, but sold 5 of each product on different dates the report would show the 3 products only once and give only the first QTY, Sold Date, & Price). The subtotal will say 15 sold and a total price for all 15.

How do I setup my report to list the product repeatedly each time it has been sold. It looks good when I go to my products form, the portal will show the complete list.

Here is an example of how it looks now:

Product QTY Sold Date Price

XYZ 1 1/1/2004 10.00

Cards 1 1/2/2004 5.00

eggs 1 1/2/2004 1.00

Total 15 80.00

This is the way I would like it displayed:

Product QTY Sold date Price

XYZ 1 1/1/2004 10.00

XYZ 1 1/2/2004 10.00

XYZ 3 1/3/2004 30.00

Cards 1 1/2/2004 5.00

Cards 4 1/4/2004 20.00

eggs 1 1/2/2004 1.00

eggs 1 1/2/2004 1.00

eggs 3 1/7/2004 3.00

Total 15 80.00

I can't seem to get this result. Any help would be great.

[-Queue-]

Why are you not running the report from the transaction table, where it would make more sense, since you are reporting transactions?

[Brewser]

I am actually pulling data from both tables since my product name is in the main table and all sales are located in a seperate table, so I can have multiple sales with the same product name. The link is the product ID for both tables.

I am new to Filemaker after spending a few years with MS Access, but this is proplexing.

I have tried adding the portal to the report, but in preview mode it will only list the number of rows I give it and it is consistant for each product. I really don't want to be limited to the number of rows for each product on the report.

Why doesn't Filemaker repeat the same product for each sale until it runs out of related records and then moves on to the next product on the preview report?

It seems pretty simple to me, but Filemaker wants something else and I can't figure out what that may be.

Thanks for all your help.

[-Queue-]

It's much easier to put a related field on a layout that only requires one related record than to try to put one on a layout that requires an undefined number of related records. Of course, that's what portals are for, to display more than one related record. Any you could use a portal if you really want to, but you'll need to guesstimate the largest number of related records, add a few to be safe, and make your portal have that many rows. Then you'll need to format the portal to slide up when fewer related records exist.

It's easier to put the report in the related file, where all the records you require exist and are uniquely related to a record in the other file. You add the productID::Name field to it, any summary fields for totals, and you're done. Then from the product file, perform a script that goes to related records (with Show only related selected), if you want to run a report counting only the found set; then calls a script in the transaction file that Shows All Records (if you would rather use all records in the file). Then it sorts them and previews/prints as desired.

[Brewser]

Thanks Queue,

I have been doing your second example, but again it only lists one of the Product Maser records and includes only one of the sales records relating to the master record. I don't want to show all products because the report would be too long. I want to show a picture of each product along with each sale for that same product.

I have done your portal example and that works, but it is still not the results I was after.

I may get a sample together to attach and let you guys play with it and see if you can come up with a better solution. I can't believe others aren't having the same issue.

Thanks again

[-Queue-]

If you are creating the report in the related file and there are, say, three transaction records related to a particular product, then putting ProductID::Name in the body should display the Name for each record. If it doesn't, then either the relationship is not quite right or there is in fact only one transaction per product. I have to leave, but others can help explain with more details.

[Brewser]

Here is a crude sample (attached) of what I am trying to accomplish. The report sample only lists one product and one sale's record. I want the report to show the same product repeat for each sale of the same product until it has no more related sales and then moves on to the next product..

Is there something I am missing in the script? Is there a problem with my relationship?

report issue sample.zip

[-Queue-]

Change your Report Sample layout to be based on the Sales table by going to Layouts -> Layout Setup and selecting Show records from Sales, in Layout Mode. Your sort in the Report Sample script shows 'Table Missing', so you'll probably want to re-specify it to sort by Products::Name.

Now you have a report like I described earlier. You want to run it from the Sales table, because this is where all your data except Name and Picture reside and each Sales record matches only one Product record. So putting the related picture and name fields in the subsummary sorted by Product::Name allows each to appear once, followed by all Sales records related to the ProductID, for the found set you've specified.

[Brewser]

Thanks That was it. Thanks again for all your help.