Numbers & Percentages

[igotit]

I have a standard Invoicing system with a Products, Invoice and Line Items file.

In my Invoice file I have a portal into my Line Items file where each line item ordered is shown for each invoice. Each line item has a "Vote" field with YES or NO radio buttons. I need your help with how to set up the calculations.

I need a calculation showing the total number of YES votes and the total number of NO votes for each product. Then another calculation field that will show this same information by percentage.

What I've tried has not worked and hope you can help. I use FMP 9 Advanced.

Thanks,

Milo

[comment]

Is the Vote field allowed to be empty?

[igotit]

Hi Comment,

No it must be YES or NO.

I just checked to see if we have it set up as "Not Empty" and it seems we no longer have it as a Must Enter because of a problem with IWP where IWP does not accept dialog boxes. Does that make sence? But we would like it as a Must Enter field for sure if we could figure out how to do it and it work with IWP!

Milo

Edited September 10, 2007 by Guest

[comment]

It's much easier to work with Boolean fields when they are defined as Number, with 1 for True/Yes and 0 (or empty) for False/No (you can format such field to display as "Yes" or "No" under Format > Number... ).

With this arrangement, a simple calculation of:

Sum ( Line Items::Vote )

will give you the number of "Yes" votes for each product, and:

Sum ( Line Items::Vote ) / Count ( Line Items::Product )

will return the percentage of "Yes" votes out of the total.

[igotit]

Hi Comment,

What I did isn’t working so I’ll tell you what I did in hopes you can see what I did wrong. BTW I’m using the Separation Model if that makes a difference.

I went into the Data file into Line Items and changed the Vote field into a number. I then went to the Interface file under Format > Number and clicked the radio button Format as Boolean “Show non-zeroes as: YES and “Show zeroes as: NO. I then went under Field/Control Setup: Display as Radio Button Set and Display values from Vote value list which is set up as Yes and No.

I then when back to the Data file and created two calculations in the Line Items file. One called DataSummaryCalc which is a Number Unstored “Sum ( Line_Items::Vote )” and another calculation called VotingSummaryPercent which is a Number Unstored “Sum ( Line_Items::Vote ) / Count ( ProductDescription)”.

When I go to my Invoices file and place the calculations on the layout I get a blank field from the DataSummaryCalc and I get a 0 from the VotingSummaryPercent field.

I sure hope you can see what I’ve done wrong.

Thanks again for your help Comment,

Milo

[comment]

The calculation fields should be defined in one of LineItems parent tables: either Invoices or Products. Which one depends on what you actually want.

I was under the impression you wanted the statistics per product. For this, you would define the calcs in the Products table, and place them on a layout of Products (if placed on a layout of Invoices, it would only show the stats of the first related product).

If you want stats per invoice, define the calc fields in the Invoices table, and place them on an invoice layout.

[igotit]

Hi Comment,

It's me again and in more trouble than before. Every time I turn around someone else asks me for things I know very little about.

As you know from my other posts, our customers are able to vote on products and when they do so we want to track trends, color preferences etc.

The fields we want to track are ZipCode, Income, HousingType, Sex etc. We are not interested in the name of who voted, we are after the number of the votes based on their demographic information.

Something like this,

INFO FROM THE “LINE ITEMS” TO

Item Number: 55257 Product Description: Williamson Blue Duck

Votes Total: 11,158 Yes=10537 No=621

INFO FROM THE “CUSTOMER” TO

Zip Code: 60606 = 377 Votes

Income: 15,000 to 20,000 = 15 Votes

Income: 21,000 to 26,000 = 09 Votes

Income: 27,000 to 32,000 = 28 Votes etc.

Housing Type: Single Family = 102

Housing Type: Condominium = 38 etc

Sex: Male = 281

Sex: Female = 96

We have 7 fields we need to track and most of them are pick lists like Income that can have up to 4 to 8 selections available. We then need to be able to look at that information in any way needed for a given report, similar to the above.

My problem is I have no idea how to do it. Comment, you’ve been good enough to jump in earlier and I must tell you that I’ve not even been able to figure out how to do what you told me about keeping track of the number of Yes Votes and No votes per customer.

I am very much challenged on this issue but it’s something I absolutely have to get right. Where can I go, or do or see that will show me what I need to do. Because right now I ask for your help and then can’t even follow it. It makes you feel like a dope. I know it’s like anything else, I’ll look at it a month from now and wonder how in the world I didn’t get such an easy principle. It’s always easy once you understand it.

But how in the world do I get all this kind of information. I know its all right here in my database, but how do I get to it?

I sure hope you can help me.

Milo

[comment]

I have followed loosely your other thread in the Cafe, and this is the first time your purpose is starting to become clear - but it's still not clear enough. Here's a question for you:

I am a household in ZIP code X, with Income Y, etc. and I have bought the same product three times, on three separate occasions. Twice I have voted YES, once NO. How should this be reflected in your stats?

[igotit]

Hi Comment,

I am a household in ZIP code X, with Income Y, etc. and I have bought the same product three times, on three separate occasions. Twice I have voted YES, once NO. How should this be reflected in your stats?

You can only vote once. Our system will only allow you to vote more than once on any given product. I guess you could come in under different user name etc, but I don't see people doing that for this kind of voting.

Thank you for staying with me Comment.

Milo

[comment]

Don't thank me yet, because I suspect we are very close to concluding this may be too complex for a forum question.

Anyway, you've just made it clear that the vote is an attribute of a unique customer/product combination. Therefore, the vote shouldn't be in the LineItems table, nor in the Customers table or the Products table. It needs to be in its own table - a join between Customers and Products. From that table you should be able to produce the reports you want (although maybe not all at once).

[igotit]

Hi Comment,

It needs to be in its own table - a join between Customers and Products.

I guess this is what confuses me. Or more apply put, one of the many things that confuses me, is how the Products TO comes into play. That is of course where the products are stored, but the record of them is in the Line Items file, which is also were the Vote field is located. The Item number and the Product Description do actually reside in Line Items because they are actual fields in Line Items that are lookups from the Product file.

You said that this may be beyond the scope of the forum and that's a little scary. But if that means I could pay someone to make a sample to show me how to do this I would be happy to pay them. At least I'd be able to keep my job.

Milo

[comment]

how the Products TO comes into play. That is of course where the products are stored, but the record of them is in the Line Items file

I don't think that is a correct statement. The LineItems table records the SALES of the products. The information about the product itself is (or at least should be) recorded in the Products table. Now, the problem here is that a product can have more sales that votes (my example above). IOW, only some LineItems records are also vote records. You want to summarize the vote records by their (related) attributes, from both Customers and Products. That's why it would be convenient to have them in a separate table, connected directly to where the required data is.

Alternatively, you could include a find in every one of your report scripts to exclude those LineItems that are NOT votes, and do similar filtering on all relationships that are used to produce such reports. That would probably be easier in terms of user interface (no need to automate a new record for each vote), but it would also add more complexity to the graph and the scripts.

[igotit]

Hi Comment,

Let me see if I can explain things better. First of all the fields in the Line Items TO are not product TO fields. They are actual Line Items fields that Look up the product Id and Product Description and store that information in the Line Items TO. The reason for this is if a product is deleted from the Products TO it remains in the Line Items TO as a product of the customer. That’s why I couldn’t understand why we kept going back to the Products TO because the information does in fact reside in the Line Items TO as well.

I know what I’m going to say next may sound strange but it’s true. Every Line Item is voted on or it does not become a line item. A customer either votes Yes or No on a product or No Vote. If it’s a No Vote the product is deleted from Line Items.

Another thing that may also sound strange is that a customer can only vote on a product once. They cannot ever have that product entered into the Line Items TO again. The system will not allow it!

One other thing you would also find strange is a customer is assigned one Invoice number on the first order and they always have that same Invoice number. They never have an invoice number other than their very first one.

Although some of the information may sound strange, I hope this clears thing up a little better.

Milo

[comment]

I hope this clears thing up a little better.

No, I'm afraid it doesn't - on the contrary. In fact, it makes no sense at all. If a customer is only assigned one invoice ever, why do you need separate tables for Customers and Invoices?

More importantly, why are products deleted from the Products table? If I understand you correctly, it's possible to have a product that has been bought n times, so there are n records of this product in the LineItems table (with all the product's details looked up from the Products table), yet no record of this product exists in the Products table? That makes no sense at all to me. What's worse, it breaks normalization and makes things like getting statistics for a product a lot harder.

Is this for real, or are you only using the invoice model as a disguise for some other activity that you are reluctant to reveal?

[igotit]

Hi Comment,

Is this for real, or are you only using the invoice model as a disguise for some other activity that you are reluctant to reveal?

The system we are using was set up as an invoicing system and we in fact use it that way for traditional invoicing of one customer, many invoices.

What I’m talking about now we added to the invoicing TO. Maybe the way to look at it is a customer buys a book from us and we generate a standard invoice as mentioned above. For this example, the customer bought a book, reads the book and comes back to the site and votes on it. That’s why we only assign them one invoice for this part of the process. Maybe It should be called something other than an Invoice but that’s what we call it here and is the logic I use when explaining it. But it is in fact set up just like I said in my other post.

As far as deleting things from our products file it’s done all the time. If it’s an item that is no longer available it’s delete from the system.

Any clearer or did I just make things worse?

Milo

[comment]

I don't know why you would delete product records. What if a product becomes available again? And do you also delete the related line items? If yes, you are deleting your business history - don't you ever want to find out how the business is doing over time? If not, it's not a very economical arrangement: instead of keeping a single set product details in the product record, you are duplicating it many times by looking it up into multiple line items.

Anyway, to get back to your reporting issue, here is, I think, the main problem with your request: It seems you want a report broken up first by product, then by zipcode, then by (1) income bracket AND (2) housing type AND (3) sex. Filemaker can easily produce 3 separate reports:

1. by product/zipcode/income group

2. by product/zipcode/housing type

3. by product/zipcode/sex

Each of these reports requires a different sort order - that's why you cannot produce them at once. A script could probably run each report in turn, gather the summary results and put them somewhere (e.g. into global field), but it is not a simple task, esp. as these three reports are supposed to intertwine in the final form.

[igotit]

Hi Comment,

The line items are never deleted because as you’ve said, “that’s our history”! And because we have it in our Line Items, we can delete it from Products. At least that’s how they do it here.

What I need more than anything is a list of how many people voted Yes and how many people voted No on each product.

The fields are in a portal from “LineItemsVoting” relationship from the Line_Itmes TO and are related to Invoices by InvoiceNumber. I don't know if you need all of that, I just thought I'd add it just in case.

EXAMPLE:

Item Number: 55257

Votes Total: 11,158 Yes=10537 No=621

As we’ve discussed the Item Number does actually reside here along with the Vote field that contains either Yes or No.

How do I build the calculation to do this? Ideally we would like to look at a single product and see that information, as well as having a scrolling list of each product showing this information.

Of all the things I need, nothing is more important than this information! Please help me by telling me how to get it.

Milo

[comment]

Well, this one is quite simple. You go to the LineItems table, and you create a new report layout, with 2 sub-summary parts, the first one by ItemNumber, and the other one by the Vote field (the layout wizard will help you with that, even create a script for you). You place you summary count field in both sub-summary parts (you can delete the Body part, since you only want summaries). Again, this is all done in the LineItems table.

Another way of doing that would be from the Products table - but that would only work for the products you haven't deleted.

[igotit]

Hi Comment,

Ok that gives me the total count of each item but it does not give me by Yes and No. How do it get that information as well?

Milo

[LaRetta]

...with 2 sub-summary parts, the first one by ItemNumber, and the other one by the Vote field

As indicated, the second sub-summary would be your yes/no vote field. You would sort first by item and then by vote field (same sort dialog). When you then place your summary count field in THAT part (for vote field), it will display and count as such:

Leading part (Item#) Put Item# field, description and summary count field in here.

Leading part (VoteField) Put Vote field and same summary count field in here.

Body - (you can delete the body)

Sort by Item# then Vote field (let FM do it all for you in the Report Wizard). Your report will display as:

Item #1 Gizmo Total Count: 320

Yes votes 120

No votes 200

Item #2 Gadget Total Count: 22

Yes votes 11

No votes 11

...etc

[bcooney]

I went into the Data file into Line Items and changed the Vote field into a number. I then went to the Interface file under Format > Number and clicked the radio button Format as Boolean “Show non-zeroes as: YES and “Show zeroes as: NO. I then went under Field/Control Setup: Display as Radio Button Set and Display values from Vote value list which is set up as Yes and No.

I don't want to interfere in this thread, bcs I'm sure too many cooks...but, your value list should be set to 1 0, not Yes and No.

[igotit]

Hi Laretta,

Something is wrong somewhere. I’m doing, or at least think I’m doing, what you’re telling me. But I’m not having the same results. Here’s what I’ve done.

Layout: Line_Items

1st Sub Summary sorted by Line_Items: ItemNbr

Fields in this sub summary Line_Items: ItemNbr & VotingSum.

2nd Sub Summary sorted by LineItems: Vote

Fields in this sub summary Line_Items: Vote & VoteSum

VoteSum is a summary field set as “Count Of Vote” (I at one time had it set as a calc field as “Sum ( Vote )” but that just gave me 0’s)

When I enter Preview mode and sort by ItemNbr and Vote Here is what I get.

#405 3

Yes 3

#408 3

Yes 3

#446 3

No 3

What I should get is

#405 3

Yes 2

No 1

#408 3

Yes 1

No 2

#446 3

Yes 2

No 1

Can you tell what’s wrong? Also is there really such a thing called “Report Wizard” and if so where is it?

Milo

[comment]

I think we could use a sample file here, preferably a simplified one.

[bcooney]

Check your value list. The vote field should contain either 1's or 0's.

[igotit]

Hi,

I have the Value list set as Yes and No and under Number Format is set to Boolean Yes and No. I at one time had it as a Radio button set but I've changed it to a Drop down list if that makes a difference.

You mentioned putting 1 and 0 in the Value list but how will a user understand what 1 and 0 mean?

What am I missing here?

Milo

[bcooney]

try this technique

yesno.zip

[igotit]

Hi Everybody,

First I want to thank you one and all and let you know that I finally figured out what was wrong. I had the Vote field set as a Number field instead of a Text field. Whoa, its either all right or its all wrong isn't it.

This has been a real lesson for me I can tell you that. My special thanks to Comment for sticking with me and the rest of you for jumping in to help. You guys are the best.

Thanks so much,

Milo

[comment]

Just a note here:

If you're using a summarized report (in the LineItems table) to view the results, you don't HAVE TO change from yes/no to 1/0 (though I would still recommend it). But if you want the view 'live' results in calculation fields (using aggregate functions, for example from the context of Products), then such change is mandatory.

[igotit]

Hi Comment,

I'm going to change it back to a number field and 1 and 0. It sounds like that’s the best way at all times, so that’s how I'll do it.

BTW is there a way to get the Yes & No to show horizontally instead of vertically? Hey I'm delighted with the way it is, I'm just asking!

Milo

[comment]

Yes - if you use the 1/0 numeric values. Then you can let go of the sub-summary by Vote part, and use summary and calculation fields in the sub-summary by ProductID part:

sYes = Total of Vote

sCount = Count of either Vote or the PrimaryKey field*

cNo = GetSummary ( sCount ; ProductID ) - GetSummary ( sYes ; ProductID )

---

(*) which one depends on whether you have any records with empty Vote, and if so, how they should be reflected in the summary.

[igotit]

Yah It's me again,

Can I trouble you guys for one more thing? After someone votes on a product we would like to show the Percentage of all votes and how everyone voted. Something like 59% voted Yes or 59% Voted No. I don't think we need both numbers just whatever is the highest percentage of the vote.

Since we do not want to influence the vote, what would be the best way to hide the percentage until they actually voted? If you guys can help me with this, I'm done and will happily go on to other things.

Thanks for all your help,

Milo

Edited October 5, 2007 by Guest

[igotit]

I hate to bother you guys again but I was hoping you could help me one last time with the question I posted above? This finishes my project and I really need your help.

Thanks,

Milo