Calculation Problem with months, How to solve it?

[Magistrate2020]

Now here's the situation, I made this database for a company (that I'm attaching at the end of the post), basically it allows you to enter in multiple lines data about customers's invoices:

- Number of Invoice

- Name of the customer

- Total net

- Total tax

- Total net+tax

- Date of payment (that if entered add the total to the "paid" column)

At the end of the page it displays:

- Last update data

- Total net

- Total taxes

- Totals Net+Taxes

- Total customers still need to pay (in red)

- Total customers paid already (in green)

My problem is, per page there's room for 50 lines where to enter data, BUT I actually don't know in advance how many "lines" will be needed per month (could be less than 50 or more than 500) and I was wondering how to make it work properly.

Solutions I've been evaluating so far:

1) The longest but the easiest to implement, duplicate this single layout for every month in the year (x11), duplicate every field, so every month could have their own records

2) Something function related, and if that is the case I definitely need some help, I was thinking to create a field called "month" where you enter the month from a dropdown list, and ask the calculation field at the end of the page to take into account the month choosen to see if to calculate or not a certain page values for example:

IF month is JANUARY make a sum of "Totale Imponibili" (of january) "Totale IVA" (of january), "Totale Fatture" (of january), "Totale da incassare" (of january), "Totale Incassato" (of january).

That for EVERY month so data wouldn't be mixed up.

This second solution would spare me the task of duplicating one hundred fields x11 and making 11 layouts, and if I can make it work you will just get a whatever number of records and distinguish the month by a field you enter manually.

Does anyone have an idea on how to do that? Is it possible? If yes, how?

Thank you in advance for any help I will receive.

Yours Faithfully.

Marko.

example.zip

[Kent Searight]

To make it do what you want, get rid of the repeating fields and create a relational database. If you don't, you may find a workaround, but I guarantee that you'll regret it later as your database grows.

Also, you don't want to duplicate layouts on a monthly basis (or any other basis). You'll want to create records instead.

[Magistrate2020]

You mean, instead of having a set of repeating fields I should design it in such a fashion that the number of 'lines' will be dependent on how many the user will need?

Forgive me the question but... what is and how to create a relational database? Any hints?

[Kent Searight]

For an explanation on creating relational databases, go to FileMaker's website.

Don't forget that FileMaker help is also easily accessed through its menu under Help. This should get you started.

[Magistrate2020]

Thank you, I'll have a look there, and probably come back with further questions later ???