Forum post
Hi everyone,
I'm fairly new to Filemaker so bear with me if there is a relatively obvious/easy solution to this. I am trying to chart the FINAL summary values of a database that uses primarily checkboxes, but I can't figure out how to do it so that it only shows the most recent data (as opposed to the entire set).
Essentially, I have
Item 1
Item 2
Item 3
...
Item 10
All have values of 1 if checkd, and 0 otherwise (I'm assuming it is 0 at least). I have two trailing summaries set for all of them, total and count. I want to do a stacked bar so that it shows the number checked on the bottom and number not checked on the top, and I want to do it so that all ten items are next to each other on the same graph. The farthest I have been able to get is graphing all ten seperately on different charts, with all of the summary values on it (i.e., if I have 8 records of all the items, all 8 summary values are graphed next to each other), but I only need the very last set. Is this possible? Hope that question makes sense (I attachhed an image to clarify).
Thanks in advance.
Answer
Am I correct that you want 10 bars--one for each question that show the number checked and the number unchecked?
I would suggest that you restructure your table so that each checkbox is the same field of a related table. A second field in the table can serve as a "label" field in order to identify each.
You can then build your chart with summary fields counting and summing your responses if you sort your records by the Label field and then chart the grouped data to get one bar for each group of records.
Be the first to like this
|
Sign in to "Like"
Yes, that is indeed correct.
I'm not sure if I understand what you are saying though. Should I create a second table that has fields related to the fields I am trying to count?
Be the first to like this
|
Sign in to "Like"
Sorry for te second message. I think I understand what you are saying, however I'm running into a few problems.
Just to test this, I only created a relationship between one of my items (lets call it item 1) and this new field in my second table (lets call it total). So now in Table 2, I have one field named total, that is set to number. When I create a record in Table 1 however, it is not updating in Table 2.
Second, how do I get the labels to transfer over as well? As of now, the only information that is being shared is the value of the checkbox, but I don't see how I can get the label from Table 1 (i.e. item 1) to be shared as well witout having to manually do it.
Be the first to like this
|
Sign in to "Like"
That's note quite what I am suggesting. I'm suggesting a change in the design of your table itself.
You appear to have these fields in Table 1:
Check box 1
check box 2
Check box 3
and so forth to checkbox 10
I am suggesting that you give Table 1 an auto-entered serial number field, PrimaryKey, if it does not already have one and relate it to table 1 like this:
Table1::PrimaryKey = ValuesSelected::ForeignKey
ValuesSelected would have these fields:
ForeignKey
Label
value
sTotalSelected, Summary, Total of Value
sCount, Summary, Count of ForeignKey
cTotalNotSelected, calculation, GetSummary (scount ; Label) - GetSummary ( sTotalSelected ; label )
You'd copy the data from Table 1 into related records in ValuesSelected. Each checkbox field would produce one related record in ValuesSelected so you end up with 10 related records in valud selected for every record in Table1. You can use a script to copy the data over and the script can supply the Label value for you as it does this.
You'd then create your chart on a valuesSelected Layout where you can sort the records by Label to group them and you'd use sTotalSelected and cTotalNotSelected as your "Y" values for the chart.
Note that after this change, you can delete the original check box fields and use a portal to the valuesSelected table for data entry directly into the new table instead of using a script to copy existing data into it.
Be the first to like this
|
Sign in to "Like"
Thanks for the response, that is making a lot more sense. I undestand everything in principle now, but I am still strugglying to get the data to copy over. Hopefully these will be the last set of questions I need to ask.
I'm struggling primarily with writing the script and getting the data to copy over. I've tried looking at other forum posts and tutorials regarding scripts, but I'm confused about the fundamentals of it. Let's assume I have one record now in Table 1, with the various checkboxes checked or not checked. There are then still currently 0 records in ValuesSelected. The script that I have consists only of Copy [Select; Table1::Item1] Paste [Select; ValuesSelected::Values]. I attempted to insert a new record command, but that only produces a new record in Table 1, but fails to add a new record to ValuesSelected or to input the data. Am I simply using the New Record command incorrectly?
Second, once I get that working, that only copies the value of 1, or 0. I've looked, I can't find a method of copying the label value over.
Once I get the first script to work, is it simply a matter of adding nine other scripts that changes only the value that is copied? And do these scripts needs to be automated to run on their own?
Be the first to like this
|
Sign in to "Like"
Make it a habit to never use the copy and paste script steps unless you have no alternative and you almost always do have an alternative. Copy will replace any data you user may have copied to the clipboard prior to running the script and having data they copied mysteriously change to something else just because they used your database both confuses and irritates your users--so avoid that at all costs. Also, copy and paste are two of a number of steps that fail silently if the referenced field is not present on the current layout--thus a future layout change that removes the field can mysteriously "break" a script that uses them.
Here's a sample script:
Freeze Window
Show All Records
Go to Record/Request/Page [First]
Loop
Set Variable [$ID ; value: Table1::PrimaryKey ]
Set variable [$Value ; value: Table1::CheckBoxField1 ]
Go to Layout [table2]
New Record/Request
Set Field [Table2::ForeignKey ; $ID]
Set Field [Table2::Value ; $Value ]
Set FIeld [Table2::Label ; "Label of value in quotes here"]
Go to Layout [original layout]
Set variable [$Value ; value: table1::checkBoxField2 ]
Go to layout [table2]
New Record/Request
Set Field [Table2::ForeignKey ; $ID]
Set Field [Table2::Value ; $Value ]
Repeat for each field in Table1
Go To layout [original layout]
Go to Record/Request/Page [next ; exit after last]
End Loop
There are two key concepts here.
Be the first to like this
|
Sign in to "Like"
Thank you so much, I managed to get that script up and running without too much of a problem (and it makes much more sense now)! You are truly a lifesaver right now.
The only problem I'm really running into now is how to get the null values transferred (I only have one checkbox, so its either 1 if checked, null if not). Currently, regardless of whether or not the box is checked, the value of 1 is transferred over to table 2. I don't want to have to make a second check box of value 0 as it will make the usability more difficult. Is there any way to get it to "copy" a null value as well?
And is there any way to set the script trigger so that it runs when new record is hit? None of the script trigger functions that Filemaker as listed will work.
Thanks again in advance.
Be the first to like this
|
Sign in to "Like"
Nevermind, the null values are working now. Also, just from doing some googling and searching in the forum, is the only way still to run a script with a new record request to write your own script and create a button?
Be the first to like this
|
Sign in to "Like"
I'm not sure that I understand the question.
Scripts can be run by clicking a button.
Scripts can be run if selected from the scripts menu or Manage | Scripts.
Scripts can run when the file opens and/or when it closes
Filemaker has been able to do these going back to versions 3 or 4.
Today's FileMaker can also respond to a number of different script triggers such as exiting a field, entering a field, changing layouts, etc.
and you can create a new record by selecting that option from the Records menu or by pressing the keyboard shortcut for it if you want to create a new record and don't want to use a script to do so--again something that Filemaker has always been able to do.
Be the first to like this
|
Sign in to "Like"