Help: Worksheet: Pop-up-List in a Spreadsheet cell

[Tobias Kern]

Hi VW-Forum,

 

Is it possible to refer to a database field (type: pop-up) in a Spreadsheet cell within a worksheet and to be able to select (list) the pop-up entries in that Spreadsheet cell?

 

Greetings and thanks for help

Tobi

Edited February 29 by Tobias Kern

[Jeff Prince]

That's an interesting question.

I know you can edit records with pop-up values within a worksheet database, I don't know how you could do that from a spreadsheet cell.  It would be handy though.

Hopefully @Pat Stanford will weigh in.

[Pat Stanford]

Simple answer is no.  There are no worksheet functions to display the multiple pop-up choices.

 

More detail about what you are trying to do and it MAY be possible with a worksheet script.

 

Here is one that takes the Record and Field names as parameters and returns the popup values.

 

Procedure PopupDefaults;
{©2024  Pat Stanford - pat@coviana.com}
{licensed under the Boost Software License 1.0}
{https://github.com/boostorg/boost/blob/master/LICENSE_1_0.txt}
{TL/DR Use as you want, attribution for source, No warranty}

{Worksheet script to provide all popup values of a Record.Field. Maximum of 50 PopupSetChoices}
{Run in worksheet using =RunScript('PopupDefaults','Record Name', 'Field Name2Index}
{Set to separate values with a colon as with defatul row height only the first value shows}
{Change the middle of the For N2:= line  from ' : '  to CH R(10) (without the space) to put on separate lines}


VAR		Rec, Fld, S3:String;
		N1,N2:Integer;
		A1: Array[1..50] of string;

BEGIN
	Rec:=WSScript_GetPrmStr(0);
	Fld:=WSScript_GetPrmStr(1);
	
	PopupGetChoices(Rec, Fld, N1, A1);
	
	S3:=A1[1];
	For N2:=2 to N1 DO S3:=Concat(S3, ' : ', A1[N2]);
	
	WSScript_SetResStr(S3);
End;
		
Run(PopupDefaults);

 

@Jeff Prince are you a magician? I was 30 seconds from posting when your post came through. But then the Forum bug that won't let you use certain (CH R) text strings in a post caught me. 😉

 

[Tobias Kern]

Hi Pat,

 

thanks for that solution, I will try it tomorrow.

It late here in Germany!

 

I wrote a request for my idea to VW Inc, I think there is a use for it.

Hopefully we'll get access to pop-up lists in spreadsheet cells soon.

 

Greetings and have a nice rest of the week

Tobi

[Silvano]

Excellent topic. For smaller tasks, you can also organize the record format data in the first few rows of the spreadsheet and use the VLOOKUP function with a few absolute address cells; if you prefer, these rows can be hidden with zero height. For more robust work, you can make a spreadsheet with just this data as a reference source, for inclusion in the pop-up fields of the same RFs and other uses. Marionette networks also work well for this, including with data from different spreadsheets, but it is better to combine them with h/vlookups in the reference spreadsheet itself if there is a lot of data and changes.