AnsweredAssumed Answered

Reports: Help in Creating and Displaying Multiple Search Criteria

Question asked by MrMunchkin on May 8, 2012
Latest reply on May 14, 2012 by philmodjunk

Title

Reports: Help in Creating and Displaying Multiple Search Criteria

Post

Hi All,

I am new to filemaker (12) and would appreciate if you could offer some advice on an area I am having trouble with when creating a report.

Backgroud:

We track ship cargoes. Tonnage coming in and tonnage going out. I have two tables. Ships and Tickets. This is a one to many relationship with ship name being being the link between the two tables. 

SHIPS

Ship Name

Date

Cargo Type

Tonnage

 

Ship Name -(one to many)- Ship Name

 

 

TICKETS

Ship Name

Sales Date

Product Type

Tonnage

Customer

 

Each sale we make in TICKETS is tonnage reduced from Tonnage in SHIPS until the ships Tonnage reaches 0.

Each of the SHIPS Cargo Type can be broken down into different product types. For example, a SHIPS cargo type could be stone rubble and when we sell this on the TICKETS Product it could be broken down into Small Size, Medium Size, Large Size.

We need to create a report which tells us a % breakdown of Small, Medium, Large size in each Cargo. For example, 20% of the cargo is Small, 50% is Medium and 30% is Large. I have managed to do all these calculatuions.

Problem:

My problem arrises when we produce a weekely report by date. Each week we produce a report showing the % of the Product Type for the sales of the specified week sorted by Ship Name. I have managed to do this. It looks similar to this.

Ship Name:A

Product Type:Small = 20%

Medium = 50%

Large = 30%

In the same report we need to show the % of the Product Type for the whole ship (not just for the selected dates) as the whole ship cargo breakdown may be a different % to the weekly breakdown. I cannot figure out how to do this. I know it should look something like this.

Weekly Date Defined

Ship Name:A

Product Type:Small = 20%

Medium = 50%

Large = 30%

 

TotalTotal For Whole Ship

Ship Name:A

Small = 10%

Medium = 63%

Large = 27%

 

I think that I need to create a column in the report to enable me to get a left side (weekly date defined total) and right side (total for whole ship). I am unusre how to do this.

I also don't know how to create a script or calculation to include all the Tonnage we have sold from the Ship when we have defined a selected ship to report and a specified date. If I search only the ship name I can get the whole ship tonnage. If I search by ship name and a specified date period I can get the weekly product % breakdown. I cannot find a way to get them both to display in the report at the same time.

I tried to create a script which omits the date search criteria for the ship, im not sure if this is the correct way to solve the problem, it is probably due to my newness to filemaker but I could not figure out how to get it to work. We have many different ships, and when I created the script it seemed to only select one pre designated ship which I could not change when I wanted to search a different ship. As I had problems trying to implement this script, I am not even sure if it would give me the result I am looking for.

I would be extremely grateful if anyone could offer any help or solutions.

Thanks

Outcomes