creating an automated table in a form based on (variable) information from other fields

This is a tricky one I think and I'm not sure it's possible.  Within a form I need to create a table.  The table needs 4 columns and needs to be created automatically depending on information within a names field "reviewers" that allows multiple entries.  For each name entered (using the address book lookup - the number of names entered will vary on each form but probably won't exceed six) we need a row created in the table, so the number of rows will depend on how many names are entered in the "reviewers" field.  The first column will contain the computed name from "reviewers".  The second column will contain a computed field which displays a tickbox which appears when that person presses a button called "Review Accepted" (the button is already contained within the form).  The third column contains a computed field which displays a tickbox when that person presses a button called review complete (button already contained within the form).  The forth column is an editable rich text field which the reviewer will use to put in a document.  

Doing the second, third and forth columns is easy enough (though I need to be able to match up the person who is pressing the button with the reviewer's name so that the tick goes in the right row), but because the "reviewers" field is variable in terms of the number of entries, I have no idea how to populate the first column, nor how to get the table itself to create the right number of rows to correspond with the number of entries in the reviewers field (I'd be happy to limit the table to 6 rows though if you need an upper limit - so I could pre-create the table with 6 rows if you can then tell me how to get each reviewer's name separated consecutively into each of the rows).  Can you help?  NB, I'm not Script literate, so if there's a formula solution I'd prefer it, but I suspect that if this can be done at all, it's going to require script, so please just be very clear about what I need to replace and where if you provide a script solution.  Thanks very much!  Maggie  
Who is Participating?

Improve company productivity with a Business Account.Sign Up

Steve KnightConnect With a Mentor IT ConsultancyCommented:
Marilyn, not saying it didn't as it's been a while :-) but did R4.6 have single category embedded views?  I agree of course it is a 'nicer', technically better way of doing things but maybe overkill, especially when the person doing the coding is (no offence) struggling with a missing bracket let alone writing what you suggest?

@If(@Elements(Reviewer)>=2;@SubSet(@SubSet(Reviewer;2)-1);""); is the typo corrected formula from above.

You could have a button next to each reviewer for each if you want to keep your code simple which either uses hide-when when Reviewer_1 != @ userName (assuming Reviewer_1 is first field in column 1 etc.) then the code in the button would just need to be something like  FIELD TickBox_1="1";  In the tickbox field make it a checkbox field, computed with the value being the fieldname, and enter the values for the field as just


Then it will show nothing but a checkbox (no text as options) and give it the value "1" if ticked, "" if not.

To do it with one button ou could do somehting like:

REM "Get position in multi-value field of the username -- may need to use some @Name() options here to make sure it matches";
REM "If not in the list then stop with a prompt";
@If(@IsError(position) | position<1 | position > 6;@Return(@Prompt([OK];"Error";"You are not in the reviewer list"));"");
REM "Make up field name called Reviewer_x where x is position in multi-value field";
@SetField("Reviewer_" +@Text(position);"1");

Sjef BosmanGroupware ConsultantCommented:
Just a different approach, since I dislike statements like "probably won't exceed six". Do you think it would be possible to handle this using your form in combination with a DialogBox? On the form, the fields you mentioned will mostly be computed, and on the form used for the DialogBox you can enter data from just one person? The button opening the DialogBox can be hidden when the current user is not one of the reviewers, or when he/she already reviewed the document.
Steve KnightIT ConsultancyCommented:
If you do want it as suggested (and I agree with sjef it's messy and potentially an issue with more values in field):

Row 1: @Subset(Reviewer;1)
Row 2: @If(@Elements(Reviewer>=2;@SubSet(@SubSet(Reviewer;2)-1);"");
  and hide row when this field is blank.
etc upto as many rows as you think you need max... and add a field for each checkbox in the other table cell.s

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

I agree that a table predefined with rows, and  each row set to hidden if empty,  is probably the best way to go until there is a way to create a table with fields , dynamically.

maggieballAuthor Commented:
I'm not sure I understand how the DialogBox would work in this instance (you fill in the box each time for each reviewer and it populates the table each time?).  I can always make the table bigger if need be so I think I'll go with the second option as it is easier for me to work with I think (and my users are at a fairly basic level).  Keeping the reviewers to 6 might be a worthwhile discipline in any case.  So dragon, if I can just clarify what you're suggesting.  I make a table with 4 columns and 6 rows.  Then in the first cell I put in a computed field with the formula @Subset(Reviewer;1). (that worked perfectly)  In the second row, first column, I put in your second formula (which I'm having some problems with - I get "Insufficient arguments for @Function: ")" - is there a missing parenthesis in the formula you've provided?) and so on for each row, incrementing the number to 6. (if I can just solve the tiny missing thing in the formula, I think this would work excellently for what I need).  Then in the second column I put some kind of formula that updates to a tick when the button is pressed.  But how does it match up the person pressing the button with the reviewer's name so that the tick goes in the correct row, as the reviewers won't necessarily be pressing the buttons in consecutive order?  

If you could provide me with the full formula (including the tick) for the second column, you'll save me hours of probably ineffective fiddling (I've been doing a lot of this stuff lately, but I'm really not a programmer and my dev skills are fairly paltry (trained on 4.1 to CLP about 9 years ago!).  I've increased the points accordingly.  Thanks again!  Maggie
Maggie, it's better, although more difficult, to leave your main form with the entry information, and create responses for each "row" in the table.   You can then either embed the "response" view by the parent doc ID category on the form so each new entry is updated and displayed, or you can do a computed for display field that collects the parsed values from a single column in the response view.

The benefits is that you can automatically sort your entries, and if you need to delete one, then you don't have "empty" rows.  YOu can expand (as sjef suggests) to more than six rows, or less that six rows.

Instead of a dialog box, I would use a button, this way, the user can select the value they want, and the button can process everything from adding a record to deleting records and updating the display.

Your form will run much faster without hidewhen's, and lookups in each row, and you'll be a lot happier, because it will be easier to manage.
Sorry, I don't see where this is 4.6 notes, unless Maggie says this in another question.  

I've done this the table with 6 rows way, and the problem is when you think you only need six, as sjef says, it becomes 7, then you need to delete a response, and then you need to manage when someone changes the names.

You don't have to embed views, I've done this any number of ways, including the hide-when on table rows.  If this is 4.6 and you upgrade to 5, the tables will become problematic.

If this is 5 or 6 then you have embedded views and even embedded editor that can flash a read-only version of the response, and you can have more than the "six" responses and they will always sort correctly.  The problem with the more ambitious approach is printing, so you need a button that will print the parent and the responses.

So, let's look at the logic:

You have a combo box where people select 1 to ?? names.

AAA        button   checkbox        rtcomments
BBB         button   checkbox        rtcomments
CCC        button   checkbox        rtcomments
DDD         button   checkbox        rtcomments
EEE         button   checkbox        rtcomments
FFF        button   checkbox        rtcomments

Now suppose CCC fills in the row, and then someone comes back in and replaces CCC with LLL.. you have to manage what to do with CCC's stuff, and clear the responses to use LLL

If AAA edits the document, then AAA should be able to edit only AAA's stuff and not DDD's stuff.  So, now you have  with each row, computed for display fields that hide the editable DDD's fields when it's not DDD editing the form, including the button.

Then you have a point when all fields need to be locked from editing,  and so on.

You still have a fair amount of logic to write in the table with the duplicate fields and hidewhen formulas:
Show the entire row, show the entire editable fields row, show the entire computed fields row, hide the row.

That's all I'm saying.  I've done this with response documents and flashed summaries on the parent document.  Painful in 4.6 any way you look at it, and I've always ended up with a main "Hide-when" field that does something like this:


Then my hide-when simply says: @if(@Contains(HIDEWHEN;"R1");@False;@True)

The rest of Steve's suggestion does work and is how many people approach this.  I'm just offering advice, that's all.

Steve KnightIT ConsultancyCommented:
Sorry, I clearly made the 4.6 bit up from someone elses Q read at the same time  I guess which is why I was thinking old ways...

Lots more ways to do it as suggested if r5 on...

maggieballAuthor Commented:
I'm on R6.5, but I did say that I did my training on 4.6 which is probably where Steve got confused.  As for the bracket comment, I definitely deserved it.  And there is still a bracket typo which I just can't find ("Insufficient arguments for @Function: ')'.  Marylyn, I'm afraid that Steve is right - I'm just not up to doing what you suggest.  I did think about having each reviewer in a response document but it will mean that each reviewer has to be created as a separate form and I think that the people entering reviewers won't want the extra steps.  I think that just putting hide when buttons in the right rows is probably the simplest way to do it and your suggestions will work Steve.  I've accepted you response, but (and as always I apologise for my lack of acumen - I shouldn't be doing this at all, but no one else will!) can you please correct this formula for me:  @If(@Elements(Reviewer)>=2;@SubSet(@SubSet(Reviewer;2)-1);"");   Thanks to both of you, and Marylyn, if I ever become more clever at Notes development, I'll come back and redo the database with your suggestion!  Maggie
Steve KnightIT ConsultancyCommented:
I hope you didn't take my comments as criticism, certainly wasn't mean't that way.

Looks like a semicolon was missing before the -1 by the look of it, i.e.


comes of typing on a titchy keyboard on a PDA when I'm bored being paid by someone else :-)

thanks for the points & grade.

Steve, discovered a long time ago on this board that the second you say, it can't be done, or it can only be done this way, etc., someone will invariably jump in and correct you.   I do, however, insist that the correction be accurate :)  (I really hate when someone says "No, you're wrong, sky IS NOT blue.")

My only concern is that we don't provide the person with a "quick fix"  that will, in the long run, prove to be unwieldy and unmanageable.  I've seen quite a few solutions that work well for the first 1000 documents... ??

I (and sjef) have offered the option, and the reason for why you should consider another way to approach not only the problem but the database.  And I am following this up so that other people who look to this question for an answer understand that the accepted answer may not be the only way to approach this particular problem.

You know, I've also learned that if you credit people with more abilty, they will rise to the expectation.  If you tell them that they're "not up to something," then most of the time they will believe that.

Maggie, don't let people scare you into the path of least resistance.  What I (and sjef) suggested is not only plausible, but well within your capabilities.  In fact, doing it the hide-when table way is MORE difficult that doing it with embedded views and response documents.  Our way might take a little longer to complete, but it's a far more robust solution.

Best of luck!

maggieballAuthor Commented:
Marylyn,  Steve's solution did do the trick, but I take your point about robustness.  I've never used embedded views before but I've just tested and it was pretty easy to set up what you suggested (worth knowing about as it's a handy feature).  I guess that each reviewer would have to use his or her own form and complete their information on that individual form and then the view just displays information like any view but within the main form. From a users perspective, is just a little bit harder than it would be if everything were in the one place.  Not much though (and I could probably use buttons to pull up the response docs so it felt like one form).  The one thing that I love about Notes is that it isn't a big deal to do both solutions and let the users decide which one they want.  I'll probably offer them the choice. (and sorry not to give you points on this one - I'll make it up to you though, thanks to EE I'm now considered a much better developer than I really am and people are giving me all sorts of tricky problems, which I'd probably have just said no to before I found you).  I appreciate that you guys take extra trouble to teach and discuss options rather than just provide a rote solution.  Many many thanks once again.  Maggie

The embedded view has a "category" function.  In this view, the first column can be the sorted, categorized, @lowercase(@username), or it can be the sorted, categorized by Parent UNID.  Or you can have TWO forms/views:  My Tasks, and Task Overview.  One view is sorted, categorized by username, so when they open the form they see a list of their documents that need attention.  Another view is sorted, categorized by parentUNID and contains Response documents only, and these make up the list of responses for each parent document (you can use the $REF field on the response as the category).

So, when the form opens, you can use a computed for display hidden/or unhidden field to identify the user, and the correct category will show on the embedded view - only the user's entries.  

When you set up the embedded view, look at the properties box for the view, and you will see a category property.

The other thing you can do, is associate an embedded editor with the view, so if users click on an entry in the view, the entry will appear in an embedded editor section.  Which means, if this is a response, then users can click on the responses and in the embedded editor see the snapsnot of the form.   Or you can simply let them click on the view responses to open and read them.

The other thing about the embedded view in R6 is that it can be editable.  So, you can allow users to edit the view columns, like the check marks and reasons.

At any rate, by far a much more robust solution than the 4.6 standard of hiding table rows.
Steve KnightIT ConsultancyCommented:
Agree with all of above BTW.  Having thought about it don't even think @subset was an R5 command so it was a pretty useless solution for the version I had inferred - the days of using @word and @implode etc. Yuk.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.