Solved

Application with Embedded Excel workbooks - how do you update the design without losing all the old workbooks?

Posted on 2004-04-23
10
238 Views
Last Modified: 2013-12-18
Hi

I have developed an application that uses embedded Excel workbooks & Notes/fx or field exchange. The workbooks are invoices, they work really well, & Notes summarises all the totals etc. nicely in the Views.

This is all great, except for one thing: if I ever change the design of the workbook, then from then on in, I can't see any of the old invoices any more! I have the empty workbook embedded on the Form itself, with the option to move to the Body field on creation. After upgrading the database to a newer design (which includes an updated workbook), when opening existing invoice documents, I only ever see the new blank workbook, rather than the existing workbook embedded in the document.

Can anyone explain what is happening here? How does Notes compare the version of the embedded file in the Form & the document? How does it know there's a new workbook on the Form now? And how do I stop this from happening?

I would expect it to show me the existing workbook on existing documents, & the new workbook on new documents, but this just doesn't happen!

Any thoughts would be appreciated, as I am still having to use a really old design, rather than the latest one I've been working on, so all these improvements are wasted if I can't use the new design.

Thanks.
0
Comment
Question by:PaulCutcliffe
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
10 Comments
 
LVL 24

Expert Comment

by:HemanthaKumar
ID: 10899185
When you change the design of the workbook, you have to also take care of the fx fields in the workbook. The best option would be to take a copy of the existing workbook and modify it and make sure that you don't remove any fx fields.

To keep old documents with old workbook .. keep the old form. And design a new form which will be picked by the new documents.

Or

Create two workbook tabs and define it as old format and new format, so the user can switch to what he/she wants

~Hemanth
0
 
LVL 31

Expert Comment

by:qwaletee
ID: 10899462
Did you have "store form in document" set up?  Then it would definitely work.  If not, you have to make sure you new sheet is completely compatible with your old.  It sounds like you are using FX to exchange data between the sheet and the form.  If you changed field names on EITHER the sheet OR the form, BUT NOT BOTH, then there would be a mismatch, and data would not go through.
0
 

Author Comment

by:PaulCutcliffe
ID: 10899590
I had thought of doing something like that (having two forms, or more likely, embed the workbook on a computed subform whose formula includes a Version number from the Invoice workbook) as a last resort, but figured that I must be doing something wrong for this to be required. Is that not the case? Does this always happen whenever you embed an OLE server into a Notes form? There's no known issue with R6.0.1 in this respect?

The new workbook, by the way, IS a slightly modified version of the old one, & all the Notes/fx fields remain. I designed the changes so that they would be completely compatible with the existing documents. Don't get me wrong, however; I know Notes can't see there's a new workbook, then magically enter all the data from the old one into the new one, I just figured that if you opened an existing document with a workbook embedded in it, you'd see that embedded (old) workbook, & for newly created documents with no existing workbook in the document, you'd see the (new) workbook embedded in the Form, a modified copy of which would then move to the document.

So, am I expecting too much here? Do I really need to split the form as you suggest?

Thanks for your help,

Paul
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:PaulCutcliffe
ID: 10899626
To qwaletee: No, I didn't store the Form in the Document, but I did have the option to store the OLE object in a field.
0
 

Author Comment

by:PaulCutcliffe
ID: 10899637
Wait! I thought I had the option to store the OLE object in a field set, but I haven't - that's probably the problem! I'll have a look & let you know...
0
 
LVL 24

Expert Comment

by:HemanthaKumar
ID: 10899907
If you can dynamically upload the ole object into a field then it solves the problem. Yoiu just track what is to be loaded and upload .. For that you have to keep seperate documents which will hold the attachments of the old and new versions and you embed the objects accordingly for old and new documents.

Using subforms would be a better and easy soln in this case.

0
 

Author Comment

by:PaulCutcliffe
ID: 10900210
Sorry HemanthaKumar, I don't understand your last posting - what do you mean by 'dynamically upload the ole object into a field' - do you mean manually copy the completed (old-style) workbook into the respective Notes document? Also, what do you mean by 'keep seperate documents which will hold the attachments of the old and new versions and you embed the objects accordingly for old and new documents'?

Thanks
0
 
LVL 24

Accepted Solution

by:
HemanthaKumar earned 500 total points
ID: 10900463
Let me detail..

1. Create a form which will have one field as identifier and other as richtext field .. this field will hold the format of the workbook
2. Compose doc out of this form and attach the workbook and name it as Ver1 and save , similarly do it for ver2
3. Now in your documents where fx is enabled create a richtext field which will detach the attachments from the above documents appropriately and embed it into this richtext field.. That is dynamically uploading the objects based upon the document versions.

0
 

Author Comment

by:PaulCutcliffe
ID: 12168999
Sorry, I'd forgotten about this issue, having been totaly confused by it. I have since tried updating the design of the spreadsheet again, & just like I originally thought it would, Notes simply used the new design on new docs, & the design of the existing embedded file for the old ones, so I can't see what the issue was - perhaps my Notes/Office installation at the time (think it's been rebuilt/upgraded since then) was messed up.

Anyway, it all seems to work fine now. Sorry to have kept this open for so long.

I'm gonna accept HemanthaKumar's Comment, as he's always such a helpful chap & deserves the points.

Thanks.
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

  In today’s Arena we can’t imagine our lives without Internet as we are highly used to of it. If we consider our life style just for only 2 min we found that face to face communication is swapped by e-communication.  Every Where from Works place to…
Lack of Storage capacity is a common problem that exists in every field of life. Here we are taking the case of Lotus Notes Emails, as we all know that we are totally depend on e-communication i.e. Emails. This article is fully dedicated to resolvin…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

622 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question