Script for report not working on the last page of the report

[littlered136]

Hi

 

Suddenly when i click on the all units button in the scene details layout it generates the report, but on the last page of the report it will not add up the day totals. it is showing up on all the pages before but for some reason on the last page will display a 0, but as soon as you add in new records and therefore there is a new last page, the totals then display and the new last page shows a 0. I am not sure why it is suddenly doing this. Any help would be greatly appreciated.

 

thanks

 

Sarah

Peach Crowd Breakdown.fmp12

[LaRetta]

Hi Sarah,

Unfortunately, you've taken a wrong turn in your structure.  Whenever you see multiple 'like' fields, particularly numbered 1, 2, 3 etc, it indicates that you should be using another table.  You have multiple calculations which reference other calculations and multiple summaries and the stacking of all of those summaries in your trailing grand summary is another hint you are in trouble.

You have a field called Characters List which holds multiline which totals the characters.  You should never have to do any of this.

150 - Students
10 - College Staff
8 - Student Journos
2 - College Security

Honestly, your solution need serious structural reconsideration.  Another indication of a problem ... your script holds this custom dialog:

"It is possible someone else is using this script right now. If so this script should not be run. Click Continue if this is not the case."

Scripts should be able to run without bumping into other Users so you must have portions which are not multi-user safe and it really should be changed.  I seriously suggest that you consider hiring someone to help you get your base structure set up properly - it is the foundation of your solution and when correct, you'll be able to eliminate 20-30 unstored calculations from Scenes and same in Characters and you will be able to eliminate the multiples of 10 which are stacked in your trailing grand summary.  

Your solution will also run a LOT faster than it does at present because it is deeply burdened with calculations which reference others calculations in both tables.  You are missing one table, possibly more.  I wish I had better news for you.

[littlered136]

Hi 

 

Thank you for taking the time to look at my database and give this feedback. Please do not be sorry. It is very much appreciated. I actually paid someone to write the database for me and have been learning a little bit as I go. So I guess they were not as good as they said they were then. and must have taken a template and just adjusted it. thats annoying as it sounds like i need to find someone else to write it for me. 

 

At least I know there are problems with it. I am guessing paying someone to redo it from scratch is a bette resolution then getting someone to try to fix this??

 

once again thanks for your help and advice.

[Fitch]

I don't know that you need to start from scratch, but I agree you have a structural problem. You have many scenes and many characters. When you link them, that's known as a many-to-many relationship, and the "proper" way to handle that is with an intermediary table, usually called a "join" table. The join table would contain one record for every instance of a character in a scene. It's slightly more work to set up, but gives you much more flexibility in reporting.

 

[LaRetta]

Since all you really have in the file are some reports, and since those reports will change when properly structured, I suspect starting over would be simplest.

Before even touching the file, I suggest that you identify all/most of the required tables.  This is described in terms of ordinality; one parent has many children. If one scene can have multiple characters and a single character can be in multiple scenes, this is called many-to-many and must be resolved using [ stopped ]

I see Tom just responded, explaining a join table.

In considering your solution, the critical question is how many of [something] can [something] have.  If the answer is more than one then you (usually) need a related table.  I do not know all the questions to ask but you do.  And you can start your list and then ask here on forums if they should be separate tables.

1. Can a character be in more than one scene?
2. Can a scene have more than one character?
3. Can a scene have more than one take?  etc.

Once you've identified the primary players (entities) in your file, the rest will fall into place.  I suggest that you create a file with just a few fields from each table (their primary key and any foreign key needed) and connect them in an entity-style.  This provides you with a clear map of your 1:n structure.  n:n (many-to-many) must be turned into 1:n.  This simple working file (focusing only in the graph) can be posted here with questions as you decide what you need and we can help you with it.  Or you can take shortcut and just hire someone.

I wanted to mention that, even if you hire someone, the above questions still must be answered for them to set you up properly. 

Edited November 16, 2015 by LaRetta

[LaRetta]

'cardinality' not ordinality.  Auto-correct got me!

[littlered136]

I sorry for the late reply. I got caught up at work. thank you both so much for the guidance above. I think rather then trying to find someone to do it, I will have a go myself and try. as you say someone else will still need to know it. I think the previous person used a template and adjusted it. hence why it seems messy behind...

 

I really appreciate you offering the help on here and inspiring me to have ago myself. 

 

Thanks