Hi there. Right this might sound a little complex, not too sure how to word it so I'll give it a go. Note although I reference reports below, the scope of this goes somewhat beyond just reports.
I have a `communications engine` that has been written in an access DB that I look after. It generates communications that go out in the form of print, or email. It does this the following way......
As a swerve ball, I now also need to define extra dynamic data on the fly, not stored in any specific fields in a table (assume there is no way to know how many fields a letter template will require or what size they will be in advance without making 101 temporary memo/ntext(max) fields, user requirements keep changing over time
)
So, I am currently reviewing how I might expand on this for the future. My ideal goal would be to be able to define a single block of content, bit like an HTML document, with merge fields defined.
Dear <%Firstname%>,
Isn't the weather nice today, looks like it might <b><%vbaFunctionToFormatInFancyWay([Weather])%> & "</b>, let's go to the <%Location%> for <%FoodType%>
etc.
---------
Issue 1: How to define my content.
I can re-split things up e.g. paragraph by paragraph, as I do now, with all the layout etc. that I had before
I can try and define things in a big custom block of content and write something to auto-generate something suitable for a report on the fly (remembering a single textbox doesn't have the capacity to store everything, content will have to split across pages AND a single detail/header/footer area of a report will also not be big enough to fit a single control with the content)
Maybe not using reports :/ (One thought was to pass my data off to my server, have that define/generate everything in HTML then pass it back to a browser for viewing/rendering/emailing if email content is required - I can write a big chunk of .net then to do fancy things that will require more effort if done within access.)
Any other ideas?
Issue 2: How to merge in data
I can do what I currently do, your old ="some content" & [field/function] & "some more content"
I can define tags (partly for readability), in the background I can then parse through the content and replace tags with content of fields, and where these don't exist I can use my dynamic data I've also got hidden away to populate fields.
My hidden data (which comes from various pop up windows when generating these letters, where the user can specify all sorts of extra content) is stored in XML, so its just a case of parsing the XML when formatting the letter, sticking in e.g. a dictionary and looking up values when converting the letters <%tags%> into values. Note currently all these values are stored in general purpose fields in the table my reports are based on, and its no longer fit for purpose - too many general fields.
I could flesh out my hidden data xml with all the relevant values in the reports recordset and package that off for processing elsewhere (see server/html comment earlier)
Issue 3: How to update content/letters
I can update reports one at a time just like I do at the moment. Publishing changes (especially if its just fixing some silly spelling error) becomes a chore.
I could build something that scans for updated reports from a central `letters` database, and imports new reports as and when required. Starting to get rather complex.
Ideally you just want to be able to define a letter in a table somewhere - job done.
So - there's an overview. (Full details would need a few more pages!)
Bare in mind:
User experience - they should click a button and BOOM content is coming out a printer (post any pop up's for extra information). Auditing, etc. should be transparent. Templates may be used xx,000's of times, hence the effort into automating end user simplicity.
Administration - it's great having us build the letter templates in the first place, usually cut and pasting bits out of emails or word documents users have sent us, but this takes time.
Does anyone have any ideas how they would do this?
Access 2010 btw
Many thanks
Martin
I have a `communications engine` that has been written in an access DB that I look after. It generates communications that go out in the form of print, or email. It does this the following way......
- Define a `letter` as a report, complete with all the letters contents, formatting, layout etc.
- These letters may have various fields merged in when they are run. e.g.
- ="Dear " & [Firstname] & ","
- ="Isn't the weather nice today, looks like it might <b>" & vbaFunctionToFormatInFancyWay([Weather]) & "</b>, let's go to the " & [Location] & " for " & [FoodType]
- In the background, there is a table that stores data for `a letter`, so printing a letter is simply opening the report pointing at the relevant record with relevant data attached to it.
- To generate an email, we open the report as hidden, then extract the content of the fields, process accordingly, re-format any content in an html format, and email off
- It can be a pain in the backside formatting complex letters
- There are limitations on the size of what you can stick in a textbox
- Textboxes, well, lets face it, don't do splitting properly over page boundaries
- When printing, you have to do work around/split content up more to account for paragraph boundaries or blocks of content boundaries and how they work with page breaks, and if the size of text boxes scale too much within pages, you can get big layout problems
- It doesn't account for more dynamic inclusion of user specified data that isn't stored in specific fields on the table linked to the report
- Biggest of all, putting out an update of letters means putting out an update of the client frontend.
- ....and a few more
As a swerve ball, I now also need to define extra dynamic data on the fly, not stored in any specific fields in a table (assume there is no way to know how many fields a letter template will require or what size they will be in advance without making 101 temporary memo/ntext(max) fields, user requirements keep changing over time
)So, I am currently reviewing how I might expand on this for the future. My ideal goal would be to be able to define a single block of content, bit like an HTML document, with merge fields defined.
Dear <%Firstname%>,
Isn't the weather nice today, looks like it might <b><%vbaFunctionToFormatInFancyWay([Weather])%> & "</b>, let's go to the <%Location%> for <%FoodType%>
etc.
---------
Issue 1: How to define my content.
I can re-split things up e.g. paragraph by paragraph, as I do now, with all the layout etc. that I had before
I can try and define things in a big custom block of content and write something to auto-generate something suitable for a report on the fly (remembering a single textbox doesn't have the capacity to store everything, content will have to split across pages AND a single detail/header/footer area of a report will also not be big enough to fit a single control with the content)
Maybe not using reports :/ (One thought was to pass my data off to my server, have that define/generate everything in HTML then pass it back to a browser for viewing/rendering/emailing if email content is required - I can write a big chunk of .net then to do fancy things that will require more effort if done within access.)
Any other ideas?
Issue 2: How to merge in data
I can do what I currently do, your old ="some content" & [field/function] & "some more content"
I can define tags (partly for readability), in the background I can then parse through the content and replace tags with content of fields, and where these don't exist I can use my dynamic data I've also got hidden away to populate fields.
My hidden data (which comes from various pop up windows when generating these letters, where the user can specify all sorts of extra content) is stored in XML, so its just a case of parsing the XML when formatting the letter, sticking in e.g. a dictionary and looking up values when converting the letters <%tags%> into values. Note currently all these values are stored in general purpose fields in the table my reports are based on, and its no longer fit for purpose - too many general fields.
I could flesh out my hidden data xml with all the relevant values in the reports recordset and package that off for processing elsewhere (see server/html comment earlier)
Issue 3: How to update content/letters
I can update reports one at a time just like I do at the moment. Publishing changes (especially if its just fixing some silly spelling error) becomes a chore.
I could build something that scans for updated reports from a central `letters` database, and imports new reports as and when required. Starting to get rather complex.
Ideally you just want to be able to define a letter in a table somewhere - job done.
So - there's an overview. (Full details would need a few more pages!)
Bare in mind:
User experience - they should click a button and BOOM content is coming out a printer (post any pop up's for extra information). Auditing, etc. should be transparent. Templates may be used xx,000's of times, hence the effort into automating end user simplicity.
Administration - it's great having us build the letter templates in the first place, usually cut and pasting bits out of emails or word documents users have sent us, but this takes time.
Does anyone have any ideas how they would do this?
Access 2010 btw
Many thanks
Martin
