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.
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...
Thought I'd cracked it - but unfortunately the calculations in the qty fields only work with qty 1... see below.
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!