[sims] Individual Reports - Using IF Statement in Word

[goldenjammy]

Has anyone used an IF statement in the Report. In Word you can do an IF statement with a normal Mail Merge, but I'm having a problem with thew one from SIMS in an individual report.

 

Staff enter 1 - 5 for Effort then I use a rule to decide which Box should be selected.

 

I enter the formula into the rules (by doing alt-F9 or Show Codes)

 

then enter the formula { IF {<> } = 1 "X" "" }

 

then change the check to check for a value 2 in a different box.

 

{ IF {<> } = 2 "X" "" }

 

Please add any comment/experiences/advice/solutions you have for me to do this for our school reports.

[LosOjos]

What are you trying to achieve? I have tried using If statements with Word and SIMS and they don't seem to work - the field doesn't appear to ever get updated.

 

However, if you just want to display a different word dependent on the number entered, you can do so with a gradeset (that's what we do).

 

So you'd set up a gradeset in SIMS for effort, set the grades 1 through to 5, then in the "description" box you type the text you want displayed on reports. Finally, when you add the result to your report, just choose "Result Description" rather than "Result".

[goldenjammy]

I want the IF statement to work. I have a number entered for 'Effort'. In my Word Report I have a table with phrases that equate to the values 1-5. My aim of the IF statement is to have it give a tick next to the phrase the value equates to:

 

High standard of Effort

Good Effort

Satisfactory Effort

Poor Effort

No Effort mde

 

If the aspect value is 1 then the tick will appear next to the HIgh Standard phrase etc......

 

It works fine if I do a mail merge from Excel or Access, but the SIMS merge of data does not work it seems, which is causing big issues.

[LosOjos]

The problem seems to be with putting SIMS tags in Word statement blocks; they're never updated.

 

One potential way around it (though I've not tried it so not sure if it would work) would be too put the value in a row of its own (you can colour it white and shrink the row to hide it) then use a SUM to pull the value in to the If statement block.

 

Worth a try?

[LosOjos]

Quick test appears to work (though without SIMS as I'm not in work yet). You'd replace the word "Yes" with whichever character renders a check mark in your chosen font:

 

[table=width: 700, class: grid, align: left]

[tr]

[td]3[/td]

[td]This row has white font and height of 0.01cm to hide it. The value to the left is obtained via SIMS.[/td]

[/tr]

[tr]

[td]{ If { =SUM(ABOVE) } = 1 "Yes" "" }[/td]

[td]Excellent[/td]

[/tr]

[tr]

[td]{ If { =SUM(ABOVE) } = 2 "Yes" "" }[/td]

[td]Good[/td]

[/tr]

[tr]

[td]{ If { =SUM(ABOVE) } = 3 "Yes" "" }[/td]

[td]Satisfactory[/td]

[/tr]

[tr]

[td]{ If { =SUM(ABOVE) } = 4 "Yes" "" }[/td]

[td]Poor[/td]

[/tr]

[tr]

[td]{ If { =SUM(ABOVE) } = 5 "Yes" "" }[/td]

[td]Very Poor[/td]

[/tr]

[/table]

Edited December 18, 2014 by LosOjos

[goldenjammy]

I'll have a go at that this morning after a couple of lessons. I'd had a thought about that overnight, but wondered whether WORD would be able to calculate the IF statement after inserting the data, otherwise some 'UPDATE FIELD' would be required.

 

I'll get back to you about how it works.

 

My fallback alternative, that I thought about last night, is to output ALL Aspects to Excel and do a Mail Merge from that, as the IF statement (Rule) works in that form.

[vikpaw]

That's how i've seen it done before. Pull in the values as a regular tag, then point the formula to the value.

[goldenjammy]

I'm having a problem with the IF statement. The Sum(ABOVE) works fine but there seems to be an issue with the IF statement and maybe I've got the wrong syntax structure for it.

 

{ IF { = SUM(ABOVE) } = 1 "X" "Y" }

 

It looks right but it's not beiong resolved regardless of the use of SIMS, at present.

[LosOjos]

I'm having a problem with the IF statement. The Sum(ABOVE) works fine but there seems to be an issue with the IF statement and maybe I've got the wrong syntax structure for it.

 

{ IF { = SUM(ABOVE) } = 1 "X" "Y" }

 

It looks right but it's not beiong resolved regardless of the use of SIMS, at present.

 

Are you calculating it? Hit "Ctrl + A" to highlight the whole document, then "F9" to recalculate. There's an option somewhere in Word to do this automatically when printing.

 

Also, if you're typing it in manually, make sure you use "Ctrl + F9" wherever you're adding the curled braces, otherwise Word will interpret them as text, not fields.

Edited December 18, 2014 by LosOjos

[goldenjammy]

LosOjos and VikPaw - so many thanks for your assistance, and your continued responses. We've just moved to SIMS and the use of this site will become invaluable, I guess.

 

The { IF statement } works and thanks for the advice: I'm going to put a few points here that I found issues and then anyone reading at a later point will be able to do check boxes based on a value entered.

 

1. You must have "View Field Codes" set so that you can enter the formula/function - press Alt-F9 for this, or insert from the AllCommands

2. The formula to use is {IF {=SUM(ABOVE) } = 1 "TICK" "" }

3. Make sure you use Insert FORMULA into the Table as it gives the correct curly brackets (otherwise they're see as text and not a function}

4. When Run from SIMS, do as a Word Export and then for each report generated then you'll need to do Ctrl-A (Select All) and then Update All Fields.

That seemed like a lot of work for the 1500 pupils we have (1500+ File Opens and then Update All). So:

 

Because ALL Macros are Enabled I have added a Macro which Updates the Codes automatically - avoiding 1500+ repetitions by me.

This Macro also allows the PDF Export to be done and having the tick in the right place.

 

The AutoOpen Macro is this, Go to View - Macros and Create a New Macro (I called mine AutoOpen). Don't delete any of the ones which appear there.

 

Sub AutoOpen()

 

' AutoOpen Macro ' Macro created date by G Lewis to Update Fields on Open '

Dim aStory As Range

Dim aField As Field

For Each aStory In ActiveDocument.StoryRanges

For Each aField In aStory.Fields

aField.Update

Next aField

Next aStory

End Sub

 

Once again thanks to 2 people for helping along the way - I will endeavour to assist others in the future.

[LosOjos]

One word of warning on using macros - if you upload to linked documents, you may find you get nonsensical VBA errors on opening. This is due to the VBA project having a missing reference to IndRepBaseTemplate.dot

 

I've been unable to find a reliable fix for this so far