Calc by number and Percentage

[boyblunder]

I have an Order file with a portal called “Line_Items” and in this line items file I have a field called “Vote”. This is where a customer votes Yes or No on a given line item. After a customer votes I need some calculations.

• The total number of customers who voted on this product.

• The total number who voted “Yes”.

• The total number who voted “No”.

• The total number of people who voted “Yes” by percent.

• The total number of people who voted “No” by percent. Like 77% voted Yes and 23% voted No.

Something else that is important, if it's even possible, is to only show the voting results after the customer voted Yes or No. If I need to take them to another layout to show the results, that would be doable. I'd rather not do it that way, but What do you think?

Thank you,

Carl

[Anuviel]

Yes, it is doable. A bit more information on the db would be nice.

Is it one line item that a multiple customers will vote on, or will each customer have their own line item and they will be the only one voting on certain item in a record?

Based on that the calculations can be done differently, or so I believe.

As for the results you can set up an auto enter calc with the same color as the background and once the voting is done just change the color of the fonts and it will display. There are better ways of doing that but cannot think of more atm.

Disclaimer *** Get a second opinion before listening to my advice (due to my skill level) ???

[comment]

Seems very similar to this:

http://www.fmforums.com/forum/showtopic.php?tid/189924

[boyblunder]

Hi,

Right now we have around 75 products and these are available to all customer and they can vote on any they choose.

I went to the link you provided but it confused me. I’m not sure I understand the products file part of it or how the calculations would work on that basis.

The Line_Items portal is in my Order Entry file but all of the information to be voted on is in the Line_Items portal. I’m not sure why I’d need to go into a products layout for the calculations to work, but I can add a vote field to my inventory/product file if you need me to do so.

Thank you for answering my post.

Carl

[comment]

The actual vote field should indeed be in the LineItems table. The aggregate calculations would ordinarily be in the 'parent' table, simply because the "total number of customers who voted on this product" is an attribute of a product. However, you can also have them in the LineItems table, provided you define a self-join relationship based on ProductID. This way each LineItem record can "see" it siblings and count/sum them.

[boyblunder]

Hi Comment,

I tried the calculation you recommended from the above link and could not get them working.

I tried a summary field clicking on the Voting field and clicked on “Total of” but nothing showed in the field. I then changed it to a calculation field “Sum ( Line_Items::Vote )” set as a number and that does not work either.

Can you think of anything else I can try?

However, you can also have them in the LineItems table, provided you define a self-join relationship based on ProductID. This way each LineItem record can "see" it siblings and count/sum them.

I'm sorry but I don't know what this means. I know i've read something about self-join but i'm unsure what it is.

Carl

[comment]

Do you know how to add a second occurrence of the LineItems table to the relationships graph?

If not, see Help > Working with related tables and files > Working with the relationships graph > Adding tables to the relationships graph.

After you have done that, define a relationship between the two occurrences of LineItems so:

LineItems::ProductID = LineItems 2::ProductID

Finally, define a calculation field in LineItems =

Sum ( LineItems 2::Vote )

Make sure the calculation is evaluated from the context of LineItems.