[sims] Sims.net - Custom Reports - Concatenating field values with text/strings

[cheeseslice]

Hi,

 

I am having a bit of a problem creating a custom report, I'm using the design template. What I would like to do is create a letter/statement that will be printed and posted alerting parents of outstanding fees. I would like to create one main text box that will then pull the certain information in from the database. I am using the design template option built into SIMS.

 

My problem is that I can't work out how to concatenate field values with text, I have it working with functions.

 

A working example would be:

 

="Today's date is: " & Today()

 

But if I try to do this with a field value Sims will crash:

 

="you owe me: " & Fields!something.value

 

What am I missing?

 

I spoke to a woman at our local education board today who told me this was impossible but I can't believe there is no way to do this, especially when it works perfectly with the built in functions. She did tell me that the custom reports are based on crystal reports, so I tried using ="some text" & {Fields!something.value} but this didn't work either.

 

If I simply put =Fields!something.value it works fine so the problem is only with the concatenation.

 

Any help would be appreciated.

[PhilNeal]

Are you using Fees as this has built in letters for this purpose. It looks as if you are trying to insert SQL which isn't supported.

[cheeseslice]

Are you using Fees as this has built in letters for this purpose. It looks as if you are trying to insert SQL which isn't supported.

 

Hi,

 

Yes, I am using fees ( I think). I am creating a custom letter.

 

Reports > Fees Billing > Design Templates

 

From within this I click on the Design Template button to edit the letter. If I pull over a text box I then have an expression builder that allows me to pull data from tables within the SIMS database as well as use built in functions, like Date, Time etc.

 

The SQL works perfectly if I have it in a text box of its own but when I try to combine it with text using & SIMS crashes. If however I use functions everything works perfectly so I am assuming the problem is with the syntax that I am using.

 

Thanks for replying.

[PhilNeal]

This is beyond my knowledge but I will see if I can get an answer for you.

[cheeseslice]

This is beyond my knowledge but I will see if I can get an answer for you.

 

Thanks.

 

This is annoying me so much that I am actually looking at this from home. I have just discovered another issue. If I make a change to a text box that happens to have an error in it and I make a change to correct it, or even delete the text box completely, the changes will not save to the template properly and I have to delete the template and start again from scratch.

 

This seems to be very temperamental.

[cheeseslice]

I'm answering myself now. For some reason this is really touchy but I have got it to work:

 

In the text box:

 

=String.Concant('you text goes here ',Fields!name.value,' more text can go here')

 

If anyone has a better way to do this then I'm open to suggestions, this is trial and error for me at the minute.

 

Thanks

[vikpaw]

I just place multiple boxes. Some with text, others with fields. Not the same though and most of my fields are fixed length so not the same.

I'll have a look at my template but it looks like you have it working now.

Make sure you have the right version of Adobe. I switched to adobe a while back and it works much better.

Also you can export / 'save as' the templates which is a lifesaver when you bork it

[cheeseslice]

So do you have the same problem with it not saving changes properly to fix errors?

 

I have been testing it to try an work out what is causing the problem but there doesn't seem to be any logic to it at all.

[vikpaw]

I don't know if it was that exactly. I set them up some time ago but do remember having to make backups as I kept having to restart.

 

Since then I've made some additions but I always clone and then add so I build up a list of old ones. We mostly use it for invoices and bills. So I just change the default to the new one.

[cheeseslice]

I've just discovered yet another fault!

 

If you want to put a pound sign in a text string you have to use convert.tochar and use the ascii code. This is probably the most backward system I have ever used.

[PhilNeal]

@cheeseslice sorry you're not making much progress. I will get the product manager to help but he's on leave this week. You could raise a case however which would go through normal channels.

 

Frankly I'm amazed that SQL expressions are supported at all; as a general rule I mandate that this knowledge should be unnecessary in our software. I do know that the fees software works well as I was with an Independent school a few months ago that raises £260m on a billing round and they are delighted with it!!

[cheeseslice]

Hey,

 

I finally got it all done. I suppose part of my problem is that I don't have any manuals or documentation so everything was trial and error, and because the errors were causing it to crash it meant I had to log back in, delete the template and start again each time. Although I took the advice of vikpaw and started exporting versions every time I got something working.

 

I found it very longwinded to do very simple things, an example: You owe us £500, could you please pay as soon as possible.

This would be: =String.Concat('You owe us ' + Convert.ToChar(0163), Fields!total_net_statement.Value, ', could you please pay as soon as possible.')

 

To get the date to display as 30 May 2013, I had to use this: =Day(Today()) + Month(Today()) + Year(Today())

 

I am only asked to look at these sorts of things when others get stuck so I have a very limited exposure to SIMS but I would have thought these were pretty common requirements in this sort of template.

[vikpaw]

I did it without a manual too, the help is ok but not great.

i think the reason i changed to pdf from word was for easier editing believe it or not as something we needed to do wouldn't work in word and layout was a pain.

Just to assure you you're not going mad, i tested and the same thing happened to me and i recall the some of the issues i had now.

import export is your friend. after an error, the template corrupts and then won't reset. i managed to trigger an error like you and then got rid, but a test shows the error message despite deleting the text box. i think this is due to the stupid way that you have to close the designer without saving and then save back in SIMS.

 

Good to see you've worked through the issues. I ended up spending a lot of time aligning multiple boxes, and even now, due to random little errors the boxes don't align but the output text does. I don't know why, i just did many multiple edit, test and then print runs to get it right. Tables are quite useful to aid in alignment.

 

I see you've used the string.concat function which is one of the built-in ones, so i guess the solution is to find and stick to whatever tools they provide.

 

Shame the # sign didn't work, maybe it is to do with PC settings (like on this Mac now!)...? probably not though.

 

With regards the date, i don't know how you would do this if you intend to concatenate, but if you just add the =today() function into a standalone text box, you can use the format button at the bottom to do a custom format so dd MMMM yyyy will give you what you need in one neat line.

[PhilNeal]

The product manager is now back! The functions were built in with the template designer that we bought.

 

The concatenation function and text use the single quote not the double. The format of a date field can specified in the drop down.

Edited June 4, 2013 by PhilNeal