+ Post New Thread
Results 1 to 14 of 14
MIS Systems Thread, Sims.net - Custom Reports - Concatenating field values with text/strings in Technical; Hi, I am having a bit of a problem creating a custom report, I'm using the design template. What I ...
  1. #1

    Join Date
    Apr 2011
    Posts
    52
    Thank Post
    3
    Thanked 0 Times in 0 Posts
    Rep Power
    0

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

    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.

  2. #2

    Join Date
    Sep 2006
    Location
    London
    Posts
    1,318
    Thank Post
    35
    Thanked 351 Times in 237 Posts
    Rep Power
    78
    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.

  3. #3

    Join Date
    Apr 2011
    Posts
    52
    Thank Post
    3
    Thanked 0 Times in 0 Posts
    Rep Power
    0
    Quote Originally Posted by PhilNeal View Post
    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.

  4. #4

    Join Date
    Sep 2006
    Location
    London
    Posts
    1,318
    Thank Post
    35
    Thanked 351 Times in 237 Posts
    Rep Power
    78
    This is beyond my knowledge but I will see if I can get an answer for you.

  5. #5

    Join Date
    Apr 2011
    Posts
    52
    Thank Post
    3
    Thanked 0 Times in 0 Posts
    Rep Power
    0
    Quote Originally Posted by PhilNeal View Post
    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.

  6. #6

    Join Date
    Apr 2011
    Posts
    52
    Thank Post
    3
    Thanked 0 Times in 0 Posts
    Rep Power
    0
    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

  7. #7

    vikpaw's Avatar
    Join Date
    Sep 2006
    Location
    Saudi Arabia
    Posts
    5,848
    Thank Post
    671
    Thanked 1,383 Times in 1,145 Posts
    Rep Power
    350
    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

  8. #8

    Join Date
    Apr 2011
    Posts
    52
    Thank Post
    3
    Thanked 0 Times in 0 Posts
    Rep Power
    0
    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.

  9. #9

    vikpaw's Avatar
    Join Date
    Sep 2006
    Location
    Saudi Arabia
    Posts
    5,848
    Thank Post
    671
    Thanked 1,383 Times in 1,145 Posts
    Rep Power
    350
    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.

  10. #10

    Join Date
    Apr 2011
    Posts
    52
    Thank Post
    3
    Thanked 0 Times in 0 Posts
    Rep Power
    0
    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.

  11. #11

    Join Date
    Sep 2006
    Location
    London
    Posts
    1,318
    Thank Post
    35
    Thanked 351 Times in 237 Posts
    Rep Power
    78
    @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!!

  12. #12

    Join Date
    Apr 2011
    Posts
    52
    Thank Post
    3
    Thanked 0 Times in 0 Posts
    Rep Power
    0
    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.

  13. #13

    vikpaw's Avatar
    Join Date
    Sep 2006
    Location
    Saudi Arabia
    Posts
    5,848
    Thank Post
    671
    Thanked 1,383 Times in 1,145 Posts
    Rep Power
    350
    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.

  14. #14

    Join Date
    Sep 2006
    Location
    London
    Posts
    1,318
    Thank Post
    35
    Thanked 351 Times in 237 Posts
    Rep Power
    78
    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.
    Last edited by PhilNeal; 4th June 2013 at 08:54 PM.

SHARE:
+ Post New Thread

Similar Threads

  1. [SIMS] SIM's custom report
    By Steven_Cleaver in forum MIS Systems
    Replies: 19
    Last Post: 13th May 2013, 03:31 PM
  2. [SIMS] SIMS .net Report Supplier
    By Shuriken1 in forum MIS Systems
    Replies: 7
    Last Post: 25th August 2012, 12:25 PM
  3. SIMs.net - User defined fields in reports?
    By duncane in forum MIS Systems
    Replies: 4
    Last Post: 21st September 2008, 08:07 AM
  4. SIMS.net RTF Report Problem
    By MARKR in forum MIS Systems
    Replies: 3
    Last Post: 21st February 2008, 09:54 AM
  5. SIMS.net Schedule Reports
    By manxdan in forum MIS Systems
    Replies: 4
    Last Post: 26th June 2007, 10:53 AM

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •