Hi

I want to do a script which will put a certain number (eg 100) into a repeating field.

I know how to populate the 1st repetition but is there an easy way I can program the script to put the number into the 2nd repetition if the 1st repetition is full and so on? I have 6 repetitions and I would prefer to not have to do a long winded script.

thanking in advance

Well, if there are no gaps, you could do something like:

Set Field [ RepeatingField [ Count ( RepeatingField ) + 1 ] ; 100 ]

However, I have a feeling that a repeating field is not the best choice for whatever you're trying to do here.

You are probably right. It's for an invoice table and I get a few occasions when a debtor may only make a partial payment on an outstanding invoice, so I have an AmountPaid field with 6 repepititions and I want to create a script which lets me input how much was paid on the fly (thru a custom dialog box) and then automatically inserts the amount paid in the repeating AmountPaid field.

You should have a Payments table, related to Invoices by InvoiceID. Then your script can simply go to the Payments table, create a new record and set its fields accordingly.

Ah ok, I was going about it a different (possibly stupid) way. Yes, good thinking! I'll create new records in my existing payments table and reference the relevant invoice record and just have a field which totals what's been paid in the payment table, in the invoices table.

For what it's worth, I was doing it the opposite direction

thanks !

For what it's worth, I was doing it the opposite direction

If it's any consolation to you is this the most common beginners fault!

--sd

Are repeating fields considered passe? Or are they still current.

No they are breaking 1 NF, and should only be used for utility purposes:

http://en.wikipedia.org/wiki/1NF

So your utilisation of them are plain and simple wrong!

--sd

So if you have an Invoice table and you need to generate a new invoice with different items

eg

2 Days in Studio 1 - $1500

2 Hours in Studio 2 - $60

etc

wouldn't repeating fields on the Invoice layout be useful in this scenario?

the repeating fields being UnitTime, Quantity, Studio etc

Maybe I should forget about repeasting fields. I got the idea to use them from a template that came with Filemaker.

I'm gonna do a new table and look into portals...

Some of the templates that come supplied with FileMaker Pro are (to put it politely) not leading edge in their design; some aren't even demonstrating best practice.

I think the templates with FMP 10 have been significantly improved.

Edited May 19, 200916 yr by Guest

So if you have an Invoice table and you need to generate a new invoice with different items

eg

2 Days in Studio 1 - $1500

2 Hours in Studio 2 - $60

etc

wouldn't repeating fields on the Invoice layout be useful in this scenario?

That is a proper question to ask in this context - and the answer is: MOST CERTAINLY NOT. Here too, you would have a table for the invoice's line items, with each line being a separate record - see a basic demo here:

http://fmforums.com/forum/showpost.php?post/309136/

I had a look at the demo and that's exactly what I had begun doing which is good. I've called one table the Invoice table and the other table the PopulatingInvoice table. So the portal will be on the Invoicing table. and will look like:

[color:red]1. | 19/5/09 | Studio 1 | 1 Day | $900

[color:red]2. | 20/5/09 | Studio 2 | 2 Hours | $90

just wondering how to get the [color:red]red numbers to count like that as I want that field to be in the portal

You could just type @@ in the top portal row, while in Layout mode.

I've called ... the other table the PopulatingInvoice table

I believe "line item" is the standard business/accounting term for this unit of information.

That @@ trick is so cool! So any which way I sort the rows, the numbers stay there and i've made them buttons so I can go to the relevant entry in the LineItems table if nessesary. Thanks for your help once again. I've come a long way from using those repeating fields this morning.

I just wanted to ask...for the Invoice layout, should I use sub-summaries for the LineItem section, or just keep it straight

for the Invoice layout, should I use sub-summaries for the LineItem section, or just keep it straight

I am afraid I don't understand the question.

In the portal each row is going to represent 1 day in either studio 1 or 2 or if less than a day, how many hours...

So I'd like the invoice to summarize what's being charged for.

eg. 5 days in Studio 1

1 day is Studio 2

3 hours in studio 2

maybe I should try a merge field?

Add an extra categorisation to the sort order, and change the layout to behave accordingly:

--sd

Hi,

If I want to create an invoice that looks roughly like the one below, ie with line items in a box, is it possible in a sub-summary report? I've done a few subsummary reports but not sure how to do one with a box in it

actually ive been stuffing around with vertical lines and if u get them in the right place u can kind of do it

Good question! Well sort of - take a look at what I've done to Comment's template....in the first attachment.

However could it in this thread be beneficial to see what role repeating fields in my humble opinion ONLY should play ... not as storage of data but merely as allocators of stored data for display purposes only:

http://fmcollective.com/2007/08/29/pseudoportals-with-alternating-fill/

Now Peter Vinogradov's technique only point's at specific related records and if this should be summarized would a way to do it to use Michail Edoshins "Fast Summaries" which is genuine summaries, but written to a field via the algorithm. Now these figures could be written to a global field which then is cut up by Vinogradov's technique looking at lines instead of dedicated records. But I would prefere Ugo's method...

This is what I attempt to illustrate in the second template, and your question might then be why this is a better approach? Well if it was going to be repeaters only for both storage as well as display, how would you then establish how much was pulled from the stock in total ... how would establish how the sales was from month to month of a certain product.

We need to see a database as a vessel of meaning and not yet another graphical tool to impress your boos with, if charming was all it would take as purpose, would Photoshop or Gimp be more than sufficient.

--sd

InvoicesDemo-2.zip

InvoicesDemo-3.zip

HI Soren,

I really appreciate your time mate and surely I owe you a Carlsberg. Demo 2 is quite a good looking invoice, but demo 3 is a bit closer to where I was headed. I'm gonna play around with these ideas.

thanks again

What advantage do the repeating calculation fields have over a portal?

Printing and portals doesn't always "rhyme" ... but yes as long as you get rid of the scrollbar should it be smooth sailing indeed.

Dwayne raises the issues here:

http://fmpportals.blogspot.com/2008/06/filemaker-printing-portal-information.html

--sd

I don't think that answers my question. My demo prints from the LineItems table - so you can print ANY number of line items. If you choose to limit printing to say 10 items, then a portal with 10 rows will do exactly that (with or without scroll bar). So what advantage do the repeating calculation fields bring to the game?

Comment, do you know if it's possible to use vertical lines to frame sub summary parts? ive been trying to do it but with little success

Nothing really! - until you attempt to mix info from several relations or you cut the invoice up in several parts and attempt to make sliding objects to this.

Say you wish to surround a group with subsummary parts, if needed. In a portal would that require displaying the same record twice in a portal ... to carry each grouping. In portals would you either show subsummarized data or the details, or a bit of both in each portalrow ... but if you have two lines of data shown in each portal row, would an empty field not make the portal row get narrower.

I wouldn't say the repeaters method shown wouldn't require spit and polish to behave that way. But chances are, that you might have a trick up your sleeves here?

I do vaguely recollect that Matt Petrowski once embarked on something in that direction. To make a portal show data the same way a subsummary reports without too much scripting?

--sd

Comment, do you know if it's possible to use vertical lines to frame sub summary parts?

Why not? You can use vertical lines, rectangles or even field borders. If you use lines, make sure they begin just below the part's top boundary.

Alright I think Comment had a point, and sat out to make a portal behave like a sub summary report.

...but suggestions to simplifications are very welcome! But a bit of warning ... the file uses conditional formatting to a great extend, so please investigate the template with a version fm9+...

--sd

InvoicesDemo-4.zip

I finally got it right, with the help of your tip. It was like solving a chinese puzzle, one press of the arrow button too far and it went pear shaped. Thanks comment

I should really set it up so I can generate Quotes as well. would u recommend a different table for quotes?

would u recommend a different table for quotes?

Hard to say without knowing the details of your specific situation.

would u recommend a different table for quotes?

But as such is it a classic....

http://www.fmforums.com/forum/showtopic.php?tid/185942/post/246628/hl//

Which means an echoing/resounding "NO"...

--sd

http://quotationsbook.com/quote/11371/

It is indeed, sorry for blinking with a torch light, but let me pick another quote:

Once established, bad training can become a "standard". That seems to have happened here. Consider the disastrous case study examples where people are put into more than one table. The classic case is students, classes and teachers. In some versions people are put into two tables: students and teachers. In other versions into three: students, teachers and staff. In relational theory this is not a problem, it is even encouraged. In the real world use of FM it is completely incompetent.*

Snipped from: http://network.datatude.net/viewtopic.php?f=42&t=107

Michael Harris is here definitely more beacon'ish than modest!

Perhaps is that teaching really requires a certain amount of dogmatism, not least because it tends to provoke the required questioning, without which non of us ever gets wise.

--sd

You seem to know more about this than what has been posted here. I'll stick to my "Hard to say without knowing the details of your specific situation".

Oh I changed my previous post! But you are right, without context and purpose would all plausible abstractions fit the bill ... but referring to commonly established practice, can't be too harmful.

But there is a latent problem, if the de-normalization is decided purely on hunches, David Kashels chapter about blueprinting - comes to mind.

--sd

Edited May 21, 200916 yr by Guest

referring to commonly established practice, can't be too harmful.

I see a difference between that and "an echoing/resounding "NO"".

Why not? An anarchist would immediately, try to prove it wrong by looking for exceptions, and our job is done!

What would you guess the chances are for the blueprint really exist? Why should the OP in this post be exempt from making common mistakes? The good thing is that it's questioned at all ...the bad thing is the omission of context/purpose!

--sd

I think we are going in a circle. As you have noticed, the context/purpose were omitted. Therefore, I said I couldn't answer the question. You managed to provide a categorical reply, so obviously you must know something that I don't. That's all there is to it.

I'm setting up a fairly basic accounting DB for my work. I have an Expenses table, a Products table, a LineItems table and I now have a table which generates Invoices and also a script which when the invoice is paid, adds an entry/record to the Revenue table, so all is good there. And I've dispensed with ALL repeating fields...

But I'll no doubt have a few occasions when someone calls me and asks about my company's services and will want a Quote for a job on PDF...so is it preferable to generate quotes on their own table?

I'm thinking it is better to keep them seperate from the Invoices table, but very similar. Then if someone decides they like the quote and they use my company's services then I can do a script which will generate a new Invoice record based on the relevant Quote...

Can anyone tell I have no business training? haha

I cannot answer your question without a thorough understanding of your business needs and practices. All I can do is give you a few points for consideration:

• Suppose someone likes your quote, but they want a few changes - do you want to keep a record of the original quote (or quotes - if there was a back and forth process)?

• Do you need consecutive numbering for your invoices?

• Do you keep inventory?

• Do you need to track product shipping?

All these and other factors may influence the decision to separate the tables or not. There is no single correct answer that fits all (at least not in my humble opinion).

at least not in my humble opinion

I'm of the same opinion, but the process of synchronising quotes with Invoices favours the single table with an attribute telling the status. I would see to exhaust that option first, before turning to the split. There is a difference when virtuous developers breaks rules, opposed to breaking them in pure ignorance.

--sd

Edited May 22, 200916 yr by Guest

I'm going to try two different tables for Invoices and Quotes and see how that goes. Thanks to both of you.

I'm having what I hope is a simple problem with the Tab Control on my Invoices layout...

The Tab Control has to 2 tabs, each tab contains a different portal going to the LineItems table...

I have a NewRecord button on each Tab but they're getting confused...eg sometimes i press NewRecord for Tab1 and instead of creating a new portal row on Tab1, it creates a new portal row on Tab2...how can I fix this? I'm stumped...

I am not aware of a need to synchronize quotes with invoices. What really concerns me here is the implications of a line item being dependent on its parent's status.

Mirror seems a better metaphor than synchronise, sorry for the confusion. And yes it's the itemlines which I see as common at least, but as such do quotes and invoices carry a lot of fields which would turn out redundant as well, unless both only carries key fields only.

What really concerns me here is the implications of a line item being dependent on its parent's status.

It's not what I mean here, I would have two key fields here if an item line is both belonging to quote and invoice, would the quote keys value be put into the invoice key ... what I make a prerequisite here, is that quantities not would change when the quote is accepted, all field level validations are tied up on the presense of anything in the second keyfield.

--sd

I am afraid I don't follow: are you suggesting that a change of a Quote's status to Invoice would require modifying the line items records?

A multiline key on parentside is a bit faster!

--sd

Huh? How would that help with the line status being unstored? Do you realize what this would mean - having to "Ugo-ize" just about every relationship into LineItems? How long would it take before it crawls down to a snail's pace?

Couldn't you template your approach instead, I can't see what you mean here!

--sd

I wouldn't think of it. But go ahead and try to sum the sales of a product, using only line items that belong to an invoice, not to a quote. And I mean a calculation field in Products, not a report.

I wouldn't think of it. But go ahead and try to sum the sales of a product, using only line items that belong to an invoice, not to a quote. And I mean a calculation field in Products, not a report.

That's where custom functions can come in handy such as TypeSumField: http://www.briandunning.com/cf/894