Subject: RE: Dynamic table help
I think what’s confusing me is that you are not using the usual meanings of words.
…database that uses a spreadsheet in a form built from a dynamic table.
You are saying that something in your application builds a form. A form is a design element, and while you can certainly create one from program code, for instance using DXL, I don’t think that is what you meant. Actually, I think you mean that there is code that reads data from a spreadsheet. The code creates a single document that contains all the data read from the spreadsheet. Is that correct?
When you say “dynamic table,” I think you are talking about a table on a Notes form, which grows as you add data to it. It doesn’t make sense to me that you say that the form, or document, is “built from” a dynamic table. Do you mean that you have a form that contains a dynamic table? You can’t build something from a thing it contains, because to contain something, it has to already exist before the thing that built it exists. This is a paradox.
(Only one spreadsheet is used in the entire database.)
It sounds like you’re saying that the spreadsheet is stored in the database. I have to assume it’s a file attachment or embedded object in the one document. So this document contains two copies of the table information; one in the spreadsheet, and one in several multivalue fields which each represent one column.
Users can click an ‘Add’ button to add entries to the table, and a new row is dynamically created as a result.
The user adds a row of data to the “dynamic table” in the Notes form. The result of this is that a value is appended to each of the multivalue “column” fields, and this data is somehow displayed in tabular form. How this is done may be important to answering your question. You don’t mention whether the spreadsheet is also updated at this time.
…the spreadsheet is relevant because the dynamic table actually produces a spreadsheet where each row consists of data that is relevant:
I don’t understand what you mean when you say the dynamic table “produces” a spreadsheet. A dynamic table is not an active object – it is not program code. How can it produce a spreadsheet? Do you mean that the spreadsheet is your dynamic table? In other words, that the spreadsheet is an embedded OLE object which is used to display the data from the hidden multivalue column fields? So you don’t have a table at all, dynamic or otherwise; you have a spreadsheet instead of a table. A “table” is something specific in Notes. If it’s a spreadsheet, please don’t call it a table.
I need to be able to search the contents of the table using column criteria such as name, date, item #, etc. The problem I have is each column is actually defined by a field with multi values.
Usually, the hard part is not doing the search, but displaying the results. I suspect this is the problem in your case. If the hard part really is doing the searching, I don’t see why you consider it difficult. Write LotusScript code to iterate through the elements of your multivalue fields and see which ones match the search criteria.
Any suggestions on how to grab the values of each field and build a searchable view?
Now you are specifying that you want to do your searching in a view. Are you doing this because the requirement calls for you to use a view, or because you think that’s the only way to do searching? I see no reason you couldn’t create a form action that the user would click while this big document is open. The action could ask the user for the search criteria, and build a table of the results that it would display in a dialog box. No view is involved.
Or, you could use the spreadsheet object, if you have a spreadsheet object, and rearrange and highlight the rows to make it show only the matching rows, or to show them first and in a different color, or something. (Note, however, this would require the document to be in edit mode).
It’s kind of silly to create a view that contains only one document. You could do it, by setting one column to be sorted and display multiple values in separate rows, and setting the other columns to display multiple values in separate rows and not be sorted (this last point is very important, otherwise you get all the combinations of values in the fields, rather than having the rows match in the views). But the problem with searching a view that contains only one document repeated many times, is that the search looks at the contents of the document, not the contents of the view rows. So either all your rows would match the search, or none of them would. This is not very useful from the standpoint of the user, who probably wants to see just the matching rows.
You could produce such a view by having a separate set of column fields, which contain only the subset of rows that matches the query. You would have to assign these rows each time the user did a search. However, this requires making a change to the document each time you do a search, which is very likely to lead to replication/save conflicts due to different users trying to search at the same time or in different replicas.
If I’ve understood you correctly about how the application works, I feel it was not well designed. It doesn’t make sense to have a Notes application with only one document, that creates something that looks like a view instead of just using a view. There seems to be no reason it could not have been designed to make each “row” a separate document, which would make searching in a view very simple – also sorting, editing, adding rows, deleting rows, and so on. It sounds like this was designed by an ingenious fool – someone who’s way too clever for his own good, and has to do things his own way because it would be a challenging problem to implement, instead of doing it the normal and easy and sensible way.