AnsweredAssumed Answered

Database Structure Help needed!

Question asked by rik1p on Nov 2, 2018
Latest reply on Nov 6, 2018 by rik1p

Hello, first post here so hope someone can help.

I've been a Filemaker user for around 20 years and I'm trying to tidy up an existing database but just can't get my head around how to structure this data.

I've created a simple version of what I'm trying to achieve.

Here it is below.

So each job can have a number of line items (shown in the portal).

Each portal line can also have 4 different quantities.

With each quantity I have multiple calculations - shown below are just two - number of pages x qty and charge.


Screen Shot 2018-11-02 at 10.12.07.png

Currently in the existing database it's set up like this... not ideal.. A lot of duplicated fields with only the qty changing.



So, I've tried a few options.

First off I tried Repeating fields. Seemed the easiest option, but way too many problems - especially calculating the overall total as Sum(repeating fields) adds total of all quantities.

Then I found the custom function SumRepetition() and although it worked it didn't refresh automatically when qty's were changed.

Also reading many pages it sounds like repeating fields are a big no no!


So now I've tried this...

Screen Shot 2018-11-02 at 10.13.24.png


Thought I'd cracked it - but unfortunately the calculations in the qty fields only work with qty 1... see below.

Screen Shot 2018-11-02 at 10.13.08.png


Screen Shot 2018-11-02 at 10.13.03.png


Now I'm stuck and running out of ideas.


I also worked out another way - rather than having multiple versions of the qty table in the relationship, having just one - this could work if you could display portals in portals which I don't believe you can do.


How would you structure this?


Any help would be gratefully received as it's keeping me awake at night now!