Help using "get format" for making a PDF from a data table

[#1]

Hello everyone,

I have a script where I am trying to get the format of a column and then apply that to a textbox that is in a table box.

I tried using this  col_for=col<< Get Format; but I can't use what it returns in this loop

For Each({col_name}, column_names,
	col = Column(dty, col_name);
	col_for=(col<< Get Format);

	
	insertinto(col_format_text,col_for);
	insertinto(col_width,col<< Get Display Width);
	insertinto(col_wrap,col << Get Display Width);
);

I get an error when  I try and add the col_for into the list.

The reason I am doing this is that I am later trying to use the list to set the textbox format later with this:

		for(j=1, j<=n items(column_names), j++,
				For(i=1,i<= qty_of_rows, i++,
						col_list[j] << append(Text Box(col_var_list[j][i]),<<set format(col_format[j]),<<set width(col_width[j]), << set wrap (col_width[j]));
					);
				);
			
		);
		tb << Set Shade Alternate Rows( 1 );
		tb << Set Row Borders( 1 );
		tb << Set Row Borders( 1 );
		tb << Set Column Borders( 1 );

 The full script I am working on is this and the intent is to be able to easily save a data table to a PDF for printing.  This would grab the formatting of the data table and apply it to the table box columns:

dty=current data table();
		createdate=today();
		
		column_names=dty<<get column names("string");
		qty_of_rows=N Rows(dty);

		
		col_var_list={};
		var_list={};
		col_list={};
		col_format={};
		col_format_text={};
		col_width={};
		col_wrap={};
		col_for={};
		col_for2={};

// Loop through each column name
For Each({col_name}, column_names,
	col = Column(dty, col_name);
	col_for=(col<< Get Format);

	
	insertinto(col_format_text,col_for);
	insertinto(col_width,col<< Get Display Width);
	insertinto(col_wrap,col << Get Display Width);
);

		

//old way that was not dynamic, had to set up each line 
/*

		qnval=dty:"QN Number"n << Get Values;
		coval = dty:"Created On"n << Get Values;
		wonval = dty:"Workorder"n << Get Values;
		opval = dty:"Dept"n << Get Values;
		ccval = dty:"Cause Category"n << Get Values;
		dovval = dty:"Description of Variation"n << Get Values;
		vcval = dty:"Variation Comment"n << Get Values;
		sval = dty:"Severity"n << Get Values;
*/		

		for(i=1, i<=n items(column_names), i++,
			col_var_list[i]=column(dty,column_names[i])<< Get Values;;
		);

//old way that was not dynamic, had to set up each line 
/*
		nw = New Window( "PDF Print of PVNs",<<window view("invisible"),
			tb = Table Box(
				col1 = Col Box( "QN Number" ),
				col2 = Col Box( "Created On"),
				col3 = Col Box( "Workorder"),
				col4 = Col Box( "Dept"),
				col5 = Col Box( "Severity"),
				col6 = Col Box( "Description of Variation"),
				col7 = Col Box( "Narrative Cause"),
				col8 = Col Box( "Variation Comment")
			)
		);
*/	
		nw = New Window( "PDF Print of PVNs",
			tb = Table Box()
						
		);
		for(i=1, i<=n items(column_names), i++,
			tb << append( col_list[i]=col Box(column_names[i]));
		);

//old way that was not dynamic, had to set up each line 		
/*
		For Each ({value, i}, qnval,
			col1 << append( Text Box(qnval[i]));
			col2 << append( Text Box(Format(coval[i],"Format Pattern", "<YYYY></><MM></><DD> <hh24><:><mm>"),<<set width(100)));
			col3 << append( Text Box(wonval[i]));
			col4 << append( Text Box(opval[i]));
			col5 << append( Text Box(sval[i]));
			col6 << append( Text Box(ccval [i], << set wrap (150)));
			col7 << append( Text Box(dovval[i], << set wrap (325)));
			col8 << append( Text Box(vcval[i], << set wrap (325)));
			
		);
		
*/
		for(j=1, j<=n items(column_names), j++,
				For(i=1,i<= qty_of_rows, i++,
						col_list[j] << append(Text Box(col_var_list[j][i]),<<set format(col_format[j]),<<set width(col_width[j]), << set wrap (col_wrap[j]));
					);
				);
			
		);
		tb << Set Shade Alternate Rows( 1 );
		tb << Set Row Borders( 1 );
		tb << Set Row Borders( 1 );
		tb << Set Column Borders( 1 );

		wajrn = Window( "example file" );
		wajrn << journal;
		wajrn=Current Journal();
		wajrn << Show Window( 0 );

		wajrn <<Set page setup(
			margins( .1, .1, .1, .1 ),
			scale( .8 ),
			portrait( 0 ),
			paper size( "Letter" ) 
		  );
		  wajrn << Set Print Headers(""/*left*/, ""/*center*/,""/*right*/);
		  wajrn << Set Print Footers(""/*left*/, ""/*center*/,"Updated on " || char(MDYHMS(createdate)));
		  
		//wajrn <<Save PDF("Put in file location here");
		
		wajrn<<close window;
		nw<<close window;

I attached a table that I have been working with along with this script.

Thanks for any help!

Steve

[#2]

<< Get Format returns format expression which is causing issues when you add it to the list using Insert Into. You could wrap it with Name Expr

Names Default To Here(1); 

dt = open("$SAMPLE_DATA/Big Class.jmp");

myformat = Column(dt, "name") << get format;

res = {};
Try(
	Insert Into(res, myformat);
,
	show(exception_msg);
);

// Wrap it with Name Expr
Insert Into(res, Name Expr(myformat));

show(res);

Also, I would suggest using Associative Array instead of lists as they tend to be easier to read and manage in cases like this.

[#3]

Also, I don't think Text Box() has a format property as it is just text -> you will have to format the text you are inputting to the text box.

Edit:

Names Default To Here(1); 

dt = open("$SAMPLE_DATA/Big Class.jmp");
aa = Associative Array();

For Each({colname}, dt << Get Column Names("String"),
	f = Column(dt, colname) << get format;
	aa[colname] = Name Expr(f);
);

For Each({{colname, f_expr}}, aa,
	val = dt[1, colname];
	Try(
		r = Eval(Insert(Name Expr(f_expr), val, 1));
		Write("\!N\!NCol: \!"", colname, "\!" value: " , val, " formatted to \!"", r, "\!"");
	, // strings won't be formatted
		Write("\!N\!NCol: \!"", colname, "\!" not formatted ", exception_msg);
	); 
);

Write();

[#4]

Thanks for the help @jthi !

So where I am stuck now is the formatting of the date column. Using your help I have the script to here (haven't switched over to AA's yet) and this works great except being able to format the dates:

		dty=current data table();
		createdate=today();
		
		column_names=dty<<get column names("string");
		qty_of_rows=N Rows(dty);

		
		col_var_list={};
		var_list={};
		col_list={};
		col_format={};
		col_format_text={};
		col_width={};
		col_wrap={};
		col_for={};
		col_for2={};

// Loop through each column name
For Each({col_name}, column_names,
	col = Column(dty, col_name);
	col_for2=(col<< Get Format);
	col_for=char(col<< Get Format());

	
	insertinto(col_format,Name Expr(col_for2));
	insertinto(col_width,col<< Get Display Width);
	insertinto(col_wrap,col << Get Display Width);

);

		for(i=1, i<=n items(column_names), i++,
			col_var_list[i]=column(dty,column_names[i])<< Get Values;;
		);

		nw = New Window( "PDF Print of PVNs",
			tb = Table Box()			
		);
		
		for(i=1, i<=n items(column_names), i++,
			tb << append( col_list[i]=col Box(column_names[i]));
		);
		
		for(j=1, j<=n items(column_names), j++,
				For(i=1,i<= qty_of_rows, i++,
						col_list[j] << append(Text Box(col_var_list[j][i],<<set width(col_width[j]), << set wrap (col_wrap[j])));
						col_list[j] << append(Text Box(col_var_list[j][i],<<set width(col_width[j]), << set wrap (col_wrap[j])));
					);
				);
			
		tb << Set Shade Alternate Rows( 1 );
		tb << Set Row Borders( 1 );
		tb << Set Row Borders( 1 );
		tb << Set Column Borders( 1 );

		wajrn = Window( "example file" );
		wajrn << journal;
		wajrn=Current Journal();
		wajrn << Show Window( 0 );

		wajrn <<Set page setup(
			margins( .1, .1, .1, .1 ),
			scale( .8 ),
			portrait( 0 ),
			paper size( "Letter" ) 
		  );
		  wajrn << Set Print Headers(""/*left*/, ""/*center*/,""/*right*/);
		  wajrn << Set Print Footers(""/*left*/, ""/*center*/,"Updated on " || char(MDYHMS(createdate)));
		  
		//wajrn <<Save PDF("Put in file location here");
		
		wajrn<<close window;
		nw<<close window;	

I was using this before to format it:

col2 << append( Text Box(Format(coval[i],"Format Pattern", "<YYYY></><MM></><DD> <hh24><:><mm>"),<<set width(100)));

but this code does not work well with what is being pulled from the  "Get Format" function from the data table as it returns something that would use <<set format  where as I need to use format(x,formatstring,<options>) per above and I can't figure out how to get just the string part of what is returned from Get Format.  I tried this: 

col_for=char(col<< Get Format());

and while it comes in as a string it has a bunch of \! in it like this ("Format(\!"m/d/y h:m:s\!", 27, 3)") .  Any to get rid of them?  I'm thinking so type of regex?  Once they are gone I think I can use that in the Format() function.

Steve

[#5]

I would use Expressions

Names Default To Here(1);

dt = New Table("Untitled",
	Add Rows(1),
	Compress File When Saved(1),
	New Column("Column 1",
		Numeric,
		"Continuous",
		Format("Format Pattern", "<YYYY><-><MM><-><DD><'T'><hh24><::><mm>", 16),
		Input Format("m/d/y"),
		Set Selected,
		Set Values([3660681600])
	)
);

my_format = Column(dt, 1) << Get Format;
formatted_expr = Insert(Name Expr(my_format), 3660681600, 1);
Show(formatted_expr, Eval(formatted_expr));

You should have the correct Format function which is just missing the value you wish to format -> use Insert to add the value as first argument inside the Format function

formatted_expr = Format(3660681600, "Format Pattern", "<YYYY><-><MM><-><DD><'T'><hh24><::><mm>", 16);
Eval(formatted_expr) = "2020-01-01T00:00";

[#6 · Accepted solution]

Thanks @jthi !

I got it with all your help! 

//rev 6-27-25	
pickdir="blank";
		

nw2=new window("PDF Print set up",
			Panelbox("Please set the PDF save settings",
						Lineup Box(
							hlistbox(Text Box("Margin left   "),neb1=numbereditbox(0.1)),
							hlistbox(Text Box("Margin top      "),neb2=numbereditbox(0.1)),
							hlistbox(Text Box("Margin right"),neb3=numbereditbox(0.1)),
							hlistbox(Text Box("Margin bottom"),neb4=numbereditbox(0.1)),
							hlistbox(Text Box("Scale            "),neb5=numbereditbox(0.8)),
							hlistbox(Text Box("Orientation"),cb1=combobox({"Portrait", "Landscape"})),
							hlistbox(Text Box("Paper Size"),cb2=combobox({"Letter", "Tabloid"}))
						)

					),
					hlistbox(Buttonbox("print",
												neb1_value=neb1<<get; 
												neb2_value=neb2<<get;
												neb3_value=neb3<<get; 
												neb4_value=neb4<<get; 
												neb5_value=neb5<<get;
												cb1_value=cb1<<get; if(cb1_value==1,cb1_value=1,cb1_value=0);
												cb2_value=cb2<<get; if(cb2_value==1,cb2_value="Letter",cb2_value="Tabloid");
												rb_value=rb<<get selected;
												
												///////////////////start of main script
													dt=current data table();
														createdate=today();
														dtname=dt<<getname;
														dt<<clear select();
														dt << Clear Column Selection();
														
														col_aa={};
														column_names=dt<<get column names("string");
														column_names_numeric=dt<<get column names(Continuous,"string");
														eval(eval expr(col_aa=associative array({expr(column_names_numeric[1])},{expr(1)})));
														for(i=2, i<= n items(column_names_numeric), i++,
															eval(eval expr(col_aa[expr(column_names_numeric[i])]=expr(i)));
														);

														qty_of_rows=N Rows(dt);

														
														col_var_list={};
														var_list={};
														col_list={};
														col_format={};
														col_format_text={};
														col_width={};
														col_wrap={};
														col_for={};
														col_for2={};

												// Loop through each column name
												For Each({col_name}, column_names,
													col = Column(dt, col_name);
													col_for2=col<< Get Format;
													
													insertinto(col_format,Name Expr(col_for2));
													insertinto(col_width,col<< Get Display Width);
													insertinto(col_wrap,col << Get Display Width);

												);

														for(i=1, i<=n items(column_names), i++,
															col_var_list[i]=column(dt,column_names[i])<< Get Values;;
														);

														try(close(wajrn,no save));
														nw = New Window( dtname||" journal",<<WindowView( "Invisible" ),
															tb = Table Box()			
														);
														
														for(i=1, i<=n items(column_names), i++,
															tb << append( col_list[i]=col Box(column_names[i]));
															
														);
													
													eval(eval expr(	
														for(j=1, j<=n items(column_names), j++,
																For(i=1,i<= qty_of_rows, i++,
																		if(col_aa<<contains(column_names[j])==1,
																			formatted_expr = Insert(name Expr(col_format[j]), dt[i,j], 1);
																			col_list[j] << append( txtb=Text Box(formatted_expr));
																			txtb<<set width(col_width[j]);
																			,
																			col_list[j] << append(Text Box(col_var_list[j][i],<<set width(col_width[j]), << set wrap (col_wrap[j])));
																		)
																		
																	);
																)
														));	
															
														tb << Set Shade Alternate Rows( 1 );
														tb << Set Row Borders( 1 );
														tb << Set Row Borders( 1 );
														tb << Set Column Borders( 1 );
												
												wajrn = Window( dtname||" journal" );
												wajrn << journal;
												wajrn=Current Journal();
												wajrn << Show Window( 0 );

												wajrn <<Set page setup(
													margins( neb1_value, neb2_value, neb3_value, neb4_value ),
													scale( neb5_value ),
													portrait( cb1_value ),
													paper size( cb2_value ) 
												  );
												  
												  wajrn << Set Print Headers(""/*left*/, ""/*center*/,""/*right*/);
												  wajrn << Set Print Footers(""/*left*/, ""/*center*/,"Updated on " || char(MDYHMS(createdate)));
												  
												 if (pickdir=="blank",
															pickdir=pick directory();
												 );
												wajrn <<Save PDF(pickdir||"/"||dtname||".pdf");
												if(rb_value=="Yes",
													open(pickdir||"/"||dtname||".pdf");
													);
												//close(wajrn,"Nosave");
												wajrn<<close window("Nosave");
												nw<<close window;
													
													
													
												)	,Buttonbox("cancel",nw<<close window; nw2<<close window;close(wajrn,no save);), textbox("Open PDF?"),rb = Radiobox( {"Yes", "No"}))
			);
	


		
	

[#7]

If you want to let user change all the pdf settings you can use Show Page Setup(1) with << Save PDF

Names Default To Here(1);

w = New Window("Window", Text Box("Page Setup Test"));

w << Set page setup(
	margins(0.5, 0.5, 0.5, 0.5),
	scale(1),
	portrait(1),
	paper size("Letter")
);

w << Save PDF("$TEMP/test.pdf", Show Page Setup(1));

But I think it might be a better idea to build your own window which would contain the path selection and the pdf settings which you want to let user control.