Assistance with report and overall Solution

[randar]

Hi,

I am trying to create a report from a related table and do not know how to Total one field from all records? I was hoping someone could help me with it and give me any suggestions for my database? Also If I could send my project to someone for assistance?

Goto MOCC Revenue report layout. This is where Im having problems?

Username: Admin Password: 1113

Thanks

Randar

MOCC_Conference_Center.zip

Edited March 30, 2008 by Guest

[Lee Smith]

Hi randar, and welcome to the Forum.

You can post a copy of your file here. Be sure there isn't any confidential information in them.

To [color:blue]Add a file, first you will need to:

Zip you files, and then note where the file is located on your hard drive.

Next, make a [color:red]Reply to this topic, and make a comment in the Body. Before Adding the Post,

Below the Body, you need to locate the File Attachments:, and click on [color:red]Manage files

The [color:blue]MANAGE FILES ? box will appear. Note, there is a ? next to the edit box title that explains things too.

Click on [color:blue]Choose File

find the file on your HD

Click on [color:blue]Add the file will load, and then appear in the little box below Finish

Click on [color:blue]Finish

Lee

[randar]

Here is my FM solution.

[randar]

here it is again.

Username: Admin Password: 1113

MOCC_Conference_Center.zip

Edited March 30, 2008 by Guest

[Fenton]

To total all records of the Found Set, you'd use a Summary field. Look in FileMaker Help for that. To total all records of an entire table, regardless of found set, you'd need a relationship that was valid for all records, either a "constant", such as a calculation, = 1, or use a Cartesian (X) relationship operator (any fields). I don't know if Cartesian was in FileMaker 7. Then Sum ( constant relationship::field ). I see you have Sum() calculations, so you know how those work.

[randar]

I tried using a summary field but im using a calculated field from the orderline table. could you show me what you are suggesting? im new at this.

Thanks

Randy

[Fenton]

You can create a Summary field, in Orders, Total of your calculation Sum(orderlines::amt) field, for a "grand total" of the sum of the related records in OrderLines, of the found set in Orders.

[randar]

I tried what you said and it did not work?

I created a summary field in orders and it only displayed total of each record, not all added together?

Edited March 30, 2008 by Guest

[randar]

Can anyone here help me with my solution?

[mr_vodka]

I dont see youe Summary field in your sample. Create a summary field as Fenton said, or you could even create the Summary field in the OrderLines table as well. If you put that summary field on the report layout, it should give you a total of all the related records for each customer.

[bruceR]

I tried what you said and it did not work?

I created a summary field in orders and it only displayed total of each record, not all added together?

And where is the copy of the file that shows us how you tried to do this? When asking for free help please don't make us play guessing games.

[randar]

Hi, That does work but I am looking for a running total on all records not just the current customer? I also would like to total all by month for a report?

I Created a summary field but it only totals for just each record not all?

[randar]

Sorry, I had to upload the newest version. I am trying to create a report in Layout MOCC Revenue Report layout. I am taking the TotalCost field from the Invoice layout taken from Orderline table. I tried creating a summary field from Customer and tried earlier from Orderline but it does not calculate from all records for that field.

I do appreciate any help and sorry if I posted wrong solution earlier.

Thanks

Randy

[bruceR]

Sorry, I had to upload the newest version. I am trying to create a report in Layout MOCC Revenue Report layout. I am taking the TotalCost field from the Invoice layout taken from Orderline table. I tried creating a summary field from Customer and tried earlier from Orderline but it does not calculate from all records for that field.

I do appreciate any help and sorry if I posted wrong solution earlier.

Thanks

Randy

There are no summary fields anywhere in your file. Maybe you're not clear on the difference between summary fields and calculations; they are quite different. Also, it would be good to clear up a few things. You have Equipment and Maintenance headings on your report layout. Nothing anywhere in your field is identified as maintenance. So it is pretty hard to see what you are trying to do.

Edited April 4, 2008 by Guest

[randar]

Hi I am uploading my solution with TotalCostSummary summary field on the MOCCRevenue report layout. It just totals for each client and not for all records?

MOCC_Conference_Center.zip

[bruceR]

What is the purpose of the TotalCostStorage field? It is a picture field (container field) so I don't see what purpose it serves.

[Fenton]

I would second that question (likely result was supposed to be Number). But it still would be odd.

Another obvious field anomaly (what is wrong with this picture?)

AdminServiceFee + RoomEquipmenttotal + MaintenanceFee + Customer::Discount + Customer::Security - Customer::Deposit

randar,

1. Auto-enter calculations do not reset unless an included field local to their table is modified.

2. A calculation set to Global storage is going to the same for all records.

The only relationship you have to OrderLines from Customer is based on CustomerID. FillableInvoice has no records.

Basically, where do you want to see a total of what? I don't really understand what you're trying to do, and I don't want to guess.

[bruceR]

Hi,

I am trying to create a report from a related table and do not know how to Total one field from all records? I was hoping someone could help me with it and give me any suggestions for my database? Also If I could send my project to someone for assistance?

I think that might be the best bet. Expect to pay for the help; but also expect to make a lot of real progress with this particular job and with your understanding of Filemaker and database concepts.

[randar]

What would you suggest for my relationship to customer besides ID? I am having issues with the portals. both are from orderline and one is for room selection and other is for equipment. If I add something to room portal selection it adds stuff to Equipment selection?

Please, Any help is appreciated!!

Thanks

Randy

[Fenton]

What would you suggest for my relationship to customer besides ID?

I wouldn't necessarily suggest another relationship. But it seemed as if you were expecting to see the grand total of all Orders on a Customer layout. I was just pointing out that your only relationship to Orders was via the CustomerID, so therefore you could only see totals via that relationship for the one customer at a time.

In my opinion the grand total of all orders does not belong on a Customer table layout, which your MOCC Revenue report is. Revenue reports belong where the revenue is, either Orders, or OrderLines, which the Revenue 2 layout is.

I don't really understand your Rooms relationship, nor what the correlation of room(s) to an OrderLine record. It has [x] Allow creation of related records on both sides. What I believe is happening is that you're automatically creating a OrderLine record when you add a room. Since you are also using OrderLines for an Equipment record, then it looks like you've added an equipment.

It is difficult to help you, because we don't really know what your business rules are. We don't know what an Orderline really is. Is it for a Room (if so, why is the Rooms table attached with [x] Allow creation?) or a single Equipment, or either, or one of each? Unless you carefully explain this no one's going to be able to tell you what exactly to do.

The way you have it looks like an OrderLine can have 1 Equipment and/or 1 Room. The relationship to Rooms should not have [x] Allow creation on the Rooms side, at least not going THRU Orders/OrderLines. At least I don't think so.