Populating a repeating field w/ script

[christoff]

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

[comment]

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.

[christoff]

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.

[comment]

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.

[christoff]

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 !

[Søren Dyhr]

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

[christoff]

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

[Søren Dyhr]

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

[christoff]

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

[christoff]

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...

[Vaughan]

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, 2009 by Guest

[comment]

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/

[christoff]

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

[comment]

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.

[christoff]

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

[comment]

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.

[christoff]

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?

[Søren Dyhr]

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

--sd

[christoff]

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

[Søren Dyhr]

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

[christoff]

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

[comment]

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

[Søren Dyhr]

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

[comment]

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?

[christoff]

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

[Søren Dyhr]

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]

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.

[Søren Dyhr]

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

[christoff]

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?

[comment]

would u recommend a different table for quotes?

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

[Søren Dyhr]

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

[comment]

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

[Søren Dyhr]

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

[comment]

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".

[Søren Dyhr]

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, 2009 by Guest