Populating a repeating field w/ script

[comment]

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

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

[Søren Dyhr]

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

[comment]

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.

[christoff]

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

[comment]

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

[Søren Dyhr]

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

[christoff]

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

[comment]

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.

[Søren Dyhr]

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

[comment]

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?

[Søren Dyhr]

A multiline key on parentside is a bit faster!

--sd

[comment]

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?

[Søren Dyhr]

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

--sd

[comment]

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.

[bruceR]

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

[comment]

Possibly, but it's still only a workaround trying to skirt the issue of line items status being unstored. Thus it couldn't be significantly faster than a solution using relationships, while itself being limited to 10k records.

So the question is still whether this is worth the trouble just to avoid the duplication of a quote and its line items once it becomes an invoice.

[Søren Dyhr]

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.

Well I couldn't help taking up this challenge!

avoid the duplication of a quote and its line items once it becomes an invoice.

Thats what you're on about, I thought we were talking separate tables for each ... my template shows we agree more than I thought!

--sd

test.zip

[comment]

Earlier, I asked if you are suggesting modifying the status of line items records. Now it turns out the answer is yes (although you duplicate them first).

So now you have a redundancy: the quote is marked as invoiced, and its line items are marked too - and potentially, there could be a conflict between the two. I don't consider this to be a healthy data structure.

[Søren Dyhr]

and potentially, there could be a conflict between the two.

You're probably right - only I can't see why? Expand please, but bear in mind what Theo Gantos once wrote:

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

--sd

[comment]

bear in mind what Theo Gantos once wrote

Which part should I bear in mind? The one where he says I don't understand how to implement a fully relational solution?

[Søren Dyhr]

No the gist of the thread, here you call mine unhealthy without point out the weakest spot, it's quite similar to what he denotes your style of development as, inadequately normalized. Such statements are best proven true or false!

What surprises me is your reluctance to make any templates here? It's not absolutely clear how much better your approach is since there is no evidence to judge from!

--sd

[LaRetta]

Soren, Theo Gantos? You act like you're quoting an authority. Spare me ...

You'd be better off quoting your totally off-the-wall quotes you usually provide.

[Søren Dyhr]

Lets treat this boolean'ly there are 4 combinations:

1) Both Theo and Comment are BS'ing ingeniously

2) Theo knows what he's on about Comment is BS'ing ingeniously

3) Comment know what he's on about Theo doesn't

4) Both knows what they're on about

Whats you choice here LaRetta, or are anyone else qualified to point out BS in this:

http://www.tekainc.com/publications/Codd-ExtRelational1979.pdf

...judging simply on measure of people each repectively over time have helped by their advice, seems like medicine-men in ancient times got their power over crowds. In todays would would this translate to the ability to hire the best spindoctors?

But the issue is the first to call them selves selfmade men, usually are entitled or already have recieved inheritance either socially or directly pecuniary.

Should everybody be judged on their social graces and especially their ability to navigate away from the most sore toes? Sometimes can decorum be a very dangerous weapon of self-deception.

--sd

[LaRetta]

No, there are more than four choices - there are five.

5) Soren is simply bored and wants to kick up dust. You get like this when you get bored.

I vote for number 5.

[comment]

No the gist of the thread

I am not aware that the message you linked to has a gist.

here you call mine unhealthy without point out the weakest spot

I did point it out, and I thought it would be rather obvious to you, of all people. If a quote is marked as invoiced, then all of its line items must also be 'true' sales, not just quoted ones - and vice versa. Since the marks are stored in both tables, we have a redundancy and therefore a potential conflict.

If one finds that somehow a quote (i.e. NOT an invoice) has 'true' line items, one has absolutely no way of knowing which marking is wrong. I should also add here that data integrity should never depend on a script running successfully.

What surprises me is your reluctance to make any templates here? It's not absolutely clear how much better your approach is

What's so surprising? I said (at least four times!) that I will not offer a recommendation here. Therefore my approach cannot be better or worse, because there isn't one. If you must have a template, open up Filemaker, select File > New Database… and there it is.

[comment]

anyone else qualified to point out BS in this:

http://www.tekainc.com/publications/Codd-ExtRelational1979.pdf

Let me point out that the gentleman in question is NOT the author of the article.

[Søren Dyhr]

Since the marks are stored in both tables, we have a redundancy and therefore a potential conflict.

Excellent input, since "invoiced" doesn't need to be stored at all, could be unstored calc'field, the role is solely to guide the validations ...But yes a slight blunder!

then all of its line items must also be 'true' sales

Alright a quote is in my understanding of the term, a whole syndicated or interdependent whole, from which you can't go botanising picking the best lumps only to you desire. Only with one line quotes, can't you obviously expect individual lines in roles as either cashcows or lame dogs in earning.

The safest bet when quoting, is obviously to carbon copy the price list by the letter. Then why issue a quote at all ... is this the point you wish to raise here? Should it be possible to generate lines in Invoices only ... well I'll seek to solve this matter as well!

Let me point out that the gentleman in question is NOT the author of the article.

So a bluffer with borrowed feathers, but how about Mike Harris then?

--sd

[comment]

a quote is in my understanding of the term, a whole syndicated or interdependent whole, from which you can't go botanising picking the best lumps only to you desire.

So you have never seen a customer picking and haggling and getting what they want?

Then why issue a quote at all ... is this the point you wish to raise here?

No - the only point I wish to raise here is that I cannot form an opinion without a thorough understanding of the business needs and practices.

So a bluffer with borrowed feathers, but how about Mike Harris then?

I am not discussing people here, only ideas. And the idea that putting students and teachers in separate tables is ipso facto incompetent, is IMHO preposterous.

[Søren Dyhr]

So you have never seen a customer picking and haggling and getting what they want?

Oh yes, but if keeping track of these - which is implied by keeping databases in the first place ... must the quote be changed not the invoice.

--sd

[comment]

No, I don't think it must be so. One could finalize the negotiations by producing the invoice, without a matching quote. It's a business decision.

[christoff]

Seems I've missed a fair bit of discussion over the weekend! Have gone in the direction of an Invoice table and a Quotes table, linked by the LineItems table and I'll see how that goes. The LineItems table has the 2 foreign keys and each LineItems record will use one or the other. It's more the norm that I produce an invoice with no matching quote so best to keep them seperate...

I'm sure I can make 2 tables work well and the quotes will be handy for when someone calls me and says a month previous I had given them a certain hire rate. Even if I just verbally gave a quote over the phone, at least I'll have a record of what I told them. If the quote ends up turning into an Invoice I'll just import in the details. All good.

On the Invoices layout the 2 Tabs with the portals is causing me some grief. I had to give the tabs each an ObjectName and refer to the Object name in the NewRecord script so the new record would go to the right Tab. The new portal row is always at the top instead of the bottom for some reason. And if I run a script with Insert Calculated Result for a certain field, it does it for all the rows, not just the current row which I find very odd.

Let's just say my portals are doing my head in!

[comment]

I am afraid I don't follow your description. What are the two portals, and what exactly is the script? Perhaps you should attach a file showing the problem.

[christoff]

morning Comment

maybe I should be asking...if i create a new record in a portal on the Invoices layout (which creates a new record in LineItems)and the new row is created at the bottom of the portal (portal sorted by primary key ascending order) how can I get the tab to go to the first field of the new row (bottom row), rather than the first field of the first row (top row)

I can't seem to get it to do it

[comment]

if i create a new record in a portal on the Invoices layout (which creates a new record in LineItems)

Is this done by a script? If so, try adding a Go to Portal Row [Last] step somewhere towards the end of the script.

[christoff]

yep I'm using a script.

I tried

Set Variable [$A; Value:Invoices:__kpIID]

Go To Layout [LineItems]

New Record/Request

Set Field [LineItems:_kfIID ; $A]

Go To Layout [original layout]

Go To Portal Row [select; Last]

Go To Field [LineItems::DateOfUsageStart]

and it goes to the drop down calander on the first row instead. I want it to end up on the drop down calender for the last row.