Duplicating Block of Fields

[HannahS]

I'm in the process of building my base for my business (I'm a literary agent) - I've recently discovered how useful views are and now have a much better sense of what I'm doing but I have a query and I'm wondering if there's a better way to set this up or if I really just have to duplicate these fields (I'm asking because i've seen a couple of posts that say you shouldn't ever have to duplicate fields...)

So, my primary filed are the titles of each book under my agency. I then have views for the marketing info, the primary contract (in my case, UK) info etc.

The two elements I'm struggling with are:

1) Tracking international deals where there will be several countries which will each then need the same fields so that I can track for instance the contract info from the US, Germany, France etc - other than the language, the info I'll need to pull from each contract will have the same field headings. I could make this a separate table - but I'd really like the financial info to pull through to the book title so that I can track the income each book generates. 

2) Tracking royalties - every six months we'll get a statement showing sales for a six month period and any income generated - again, I would like this linked to the title so it makes sense to have it as a view from the main table, but then I'll need a set of repeating fields (Royalty statement date, period sales, period income, date paid - for instance). Is there a better way to do this? Is there a way to duplicate multiple fields at the same time or am I just going to have to keep doing them one by one? 

Thanks

[Dan_Montoya]

Hi Hannah,

If you find yourself wanting to repeat fields, you probably have something that needs its own table.  In your example, royalty payments should be their own table with their own attributes.  Royalties are then linked to the the title.  Authors would also be in a separate table as well as contracts.  Here is a quick example.

If you want to represent the native currency you might choose different fields.  I haven't dealt with that so maybe one of our other community members can comment on that.

[HannahS]

Oh, that's really helpful thank you.

In the title table where you've brought the information back - that's a linked field from the royalty tab which I know how to do - but then how have you done the total royalties? Is that a formula that's pulling figures from a different table?

I think I can handle the currency issue in the main table, and then only pull the column with the GBP conversion through so that's less of an issue.

In the table where I track foreign deals, is there a way to then pull a list of all foreign deals to one field in the title tab (because from the title tab I create a title information sheet in Extensions where I want to be able to list all the territories a title has sold0?

[Dan_Montoya]

It is a type of formula field specifically for linked records.  It is called a rollup field.  The rollup uses the SUM(values) formula for all of the records linked in the royalties field.  It is summing the royalty  payment field from the royalties table.

You could create a 2nd rollup field where the check the "only meets certain criteria" and set it to only be foreign transactions (to sum only foreign royalties).  You would then create a 3rd field that is a lookup that lists all the foreign territories from the royalties field where the "only meets certain criteria" is checked.

[Dan_Montoya · Accepted solution]

[HannahS]

Thank you so much - this has been incredibly helpful!