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.

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

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.

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

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?

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

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.

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

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.

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.

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.

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

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.

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

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.

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!

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.

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

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.

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.

I am blind here - can you post a file?

I was preparing a demo for u, this was an exact copy of what i was doing but with any irrelevant tables / layouts / scripts / value lists removed...

and when i had finished, the demo worked perfectly! Obviously I was very happy until I realised I have no idea why the demo works and my other one doesn't...

...3 hours later...just worked it out...

on the Invoices layout I had a portal going to LineItems and another portal going to Payments and the Payments portal is messing with the LineItems portal, coz when I remove it the LineItems portal works fine...must be a limit to how many portals you can have on a layout...

i would never have worked this out had I not prepared that demo for you, so you still helped!

When you call the Go to Portal Row[] step, Filemaker goes to the specified portal row of the active portal. If there is no active portal, it will select the portal that is furthest to the back of the layout.

One way to select a portal is to go to a field that exist only in that portal. For example:

...

Go To Layout [original layout]

Go To Field [LineItems::DateOfUsageStart]

Go To Portal Row [Last]

should have worked for you (note the absence of 'Select' in the last step).

I tried what you suggested and it was going to the correct row but not bringing up the drop down calender...but then I had a moment of pure genius and just repeated the line and it works!

Go To Layout [original layout]

Go To Field [LineItems::DateOfUsageStart]

Go To Portal Row [Last]

Go To Field [LineItems::DateOfUsageStart]

so I've applied the same method to a few different scripts and now it works and this means I can have all the portals back on that the layout which is a great result. Thanks again Comment for your time mate.

No, I don't think it must be so

Probably not, but what kind of role have you then to give to the quote, if the "hackling" always gets the final word, why then keep quotes in a database at all?

Why keep track of wishful thinking or failed prophesies at all - does fatalism belong to databases?

--sd

I didn't say you MUST keep them after an invoice has been finalized. Though I suppose most people would keep them at least for a reasonable period, for legal reasons and for reference.

Quotes are not exactly "wishful thinking" - they are business propositions. I know some firms that periodically analyze their quotes against consummated deals, in order to better utilize their marketing efforts.

I know some firms that periodically analyze their quotes against consummated deals, in order to better utilize their marketing efforts.

As do I.

But in you previous posts Michael have you been given the impression that such statistics quality not necessarily is tied to a rigid flow in the proceedings, how could this out of sync make any sense - if the invoiced not in some sense is a genuine subset of the quoted.

It's as if just a random pattern is established - then is it working ... but I could then suggest another approach to recognise patterns. Write all data availiable on a list, hang it on the wall ... remove yourself from the list physically. Then at a distance close one eye and begin to move you index finger into the one eye remaining open, while still looking at the list across the room.

It's obviously the same kind of data mining the monkey with the dart's tossing, did to select shares to either add or remove from the portfolio.

http://www.suntimes.com/business/roeder/208997,CST-FIN-curious14.article

The point I try to raise here is that integrity in the data should be attempted into some sort of business rule, where it's obvious what role each document should have. Keeping quotes up for just quotes sake statistically doesn't make much sense to me.

Some connection to what actually is purchased must have some weight, and not just what customers might be in want of if just it could be supplied. Think of a hot afternoon in a bank, people are cueing up, should the bank begin to sell ice-cream? Should the core business be taken with the notorious grain of salt, if something else is temporarily in demand?

The aim of data-mining should reveal both what to have on the shelves when and why as well as how much of it!

--sd

Keeping quotes up for just quotes sake statistically doesn't make much sense to me.

Some connection to what actually is purchased must have some weight, and not just what customers might be in want of if just it could be supplied.

You must be kidding. Knowing what Customers want (by itself) is invaluable. I am now totally convinced that you have never worked in a manufacturing/retail environment and you have no concept of how data translates to valuable information.

Unaccepted quotes can tell a business 1) the prices might be too high, 2) the product is not currently in favor, 3) it was presented in poor combination, 4) the business itself might not favor certain products, styles, 5) the time of year was poor and a thousand other answers.

The NOs of this world say MORE than the YESs.

I am now totally convinced that you have never worked in a manufacturing/retail environment

Correct, I have worked as sound-engineer and with management of rentals the technically matters only. So your input does indeed bring me some perspective ... I'm aware of how some tools in the warehouse attracts special kinds of business, but compared to being a freelance as I am - is the decision which pieces to let the promoter provide and which to purchase to bring with me, is down almost to completely a technical choice.

Bringing a "wardrobe" of esoteric technical stuff, boils down to such simple things as - who's going to carry them for me? Since the provided in my case is a service, would I restrict myself to a personal bag of tricks only. The invoicing is in no way detailed at all, I would not charge differently dependent on what I carry with me.

So I'm not kidding - I have to think up how data mining is done, here in form of making daft suggestions ...from where I can get corrected.

Now to your excellent points, where does it say that invoices not are a subset of the quotes, in the meaning that sometimes are numerous quotes issued without anyone seeing the bate ... then do you say other sales is then generated despite of those quote issued?

This seems to suggest it's done with blinkers each disipline - and it's then purely a managerial task to interpolate these facts ... if we take this to the service industry - wouldn't it lead to a lot of "Sorry we're out of Waldorffs" - where the obvious way to handle it, is to think up replacements - or as here in Clease's case think of the ingrediences?

Shouldn't data-mining be in charge of reordering when shortage, instead of human whims and interactions based on how good the actual interpolation is done - how well the report is read? Which part of the process is the one worth involving a conscience. Take you bodily functions, you have very little control over the heart and lungs function, but the arms and legs are pretty much the conscience's domain

--sd

I do not want to hijack this thread. And you and I can get into these discussions. So I'll respond but I must then let it go.

where does it say that invoices not are a subset of the quotes, in the meaning that sometimes are numerous quotes issued without anyone seeing the bate ... then do you say other sales is then generated despite of those quote issued?

Quotes are speculations/proposals and are generated from interaction between a customer and sales rep – two people. A quote cannot happen if it is mailed to a business from a customer. And a quote is worthless if generated by a Salesman but NEVER presented to a customer. Two people exchange ideas, barter prices, discuss product combinations for shelf space and endcaps; look sometimes years ahead and forecast needs (ie, let’s remember that in 2012, we have that convention and we’ll want 1200 additional floor displays so lets quote it now).

Invoices can be generated by a customer contacting the business. There may not (and many times won’t) be a quote. Invoices can be generated by the online website from retail customers, an invoice can be generated by a store calling the Manufacturer out of the blue and simply ordering (they know exactly what they want). Sometimes an invoice is generated because of many quotes discussed but the invoice may be totally different than any specific quote.

Quotes provide a vision of what customers are thinking, wanting, forcasting. They also provide a vision of what sales rep has tried to get the customer to envision and (when tied back to Invoices, tells the business whether the rep has succeeded). Even if something isn’t sold, it tells the business that the rep was actively working an account (although it may indicate that the rep was pushing the wrong products, approach etc). For many businesses, quotes speak volumes.

As to whether quotes should be a separate table from Invoices, it all depends upon how a business uses Quotes. If quotes usually become invoices, then they can go together. There is less chance of problem if one simply changes the status of a record from Quote to Invoice (instead of duplicating or importing the lines). But if quotes are used for in-depth negotiation and forecasting where most never become invoices then (I feel) quotes should be separate.

Data modeling is a great passion of mine and I must nip myself in the bud or I simply won't stop. So I respectfully now will drop from this thread.

I am not sure what your point is. Invoices (in the model being discussed) are not a subset of Quotes, because not every Invoice is also (or has ever been) a Quote.

However, that doesn't mean there cannot be any connection between the two: an Invoice can be linked to any Quotes that led up to it - either directly by project, or indirectly by customer. So it's possible, for example, to analyze the sales-to-quotes ratio per customer, or per salesperson.