concatenate 2 fields of many records to reduce print document lenght

[curious99]

Hi, I have a small project but can't get my head around it.

 

Let's say I have 3 records with one text field and an auto generated serial number. For example record 1 has field  = A and serial number = 1, record 2 has field = B and serial number = 2 and the third is C and 3.

 

How can I make a printout that would generate a single blob that looks like A1;B2;C3 all attached together in the shortest possible form?

 

Thanks

 

Luc

[comment]

There is no way you can print data directly from multiple records on the same line. You need to collect the data into a single field in a single record. The simplest way to accomplish this, IMHO, would be to set a global field to the result of ExecuteSQL().

 

 

 

Edited April 29, 2023 by comment

[curious99]

You're a genius. Thanks for your expertise.

 

I tested it in viewers and when I put a value in both field separator and line separator it wraps it up exactly like I wanted. The only issue, although a major one, is when I place that field in a layout, I get a ? instead of the data I see in viewer.

 

Any suggestions on that ?

 

Thanks again

 

Cheers

[comment]

51 minutes ago, curious99 said:

I get a ? instead of the data I see in viewer.

I would advise against using a calculation field here. Since you intend to print this, make it a script and have the script set the global field (of type Text) to the result of the SQL query. And you probably want to start the script by committing records - otherwise the SQL evaluation could take a while in a hosted situation.

If you must make it a calculation field, then it needs to be unstored - and therefore cannot be global.

---
As an aside:

8 hours ago, comment said:

There is no way you can print data directly from multiple records on the same line.

That's not strictly true. A list layout set up to print in columns will print data from multiple records on the same line. But I don't think that's what you want here.

 

[curious99]

Thanks again.

Getting there!

So I did integrate it in a script and it works but I do not know how to transfer the data to a global field. Sorry for noob questions : I'm a bit rusted as I'm getting back to Filemaker after many years of not touching it much. If you still have time and patience, could you point me again in the right direction ? I must say I see a lot of potential with the ExecuteSQL command which I never used before.

[comment]

It can be a bit confusing, but the Execute SQL script step has nothing to with this. All you need to do is use the Set Field script step to set the global field to a result calculated using the ExecuteSQL() function (using the same formula as you did in Data Viewer). Note that the script step has a space in its name, while the function's name is a single word.

 

[curious99]

Success!!!

 

Does all I wanted and more.

 

Thanks so much. So thankful you guided me thru this and got me to learn about ExecuteSQL. Impressive command, almost as impressive as you are :-))

You have a wonderful day,

 

cheers

[curious99]

For people that do not yet know about ExecuteSQL here is tow great ressources to rapidly get familiar with it :

 

https://www.youtube.com/@AccelerateComputerTraining/playlists

 

https://www.w3schools.com/

[curious99]

Hi there, I'm back !

All was working fine but I I've hit a snag as there is no output with this code (I tweaked the code but have not kept the original that worked fine) :

ExecuteSQL 
( 
"SELECT \"# SERIE\" , \"TITRE DE LA TÂCHE\" FROM \"Agenda_\" WHERE \"choisir aujourd'hui\" IS NOT NULL ";
 "-"; 
 "                            //"

 

but there is output if this part of the code is removed :

 

WHERE \"choisir aujourd'hui\"IS NOT NULL

 

I need that where statement but I do not see what is wrong with it.

 

Obviously my syntax is off but I do not see what is wrong. Any help would be much appreciated.

 

Thanks in advance

 

Cheers

[comment]

Could you post a file with only the Agenda_ table, having only the # SERIE , TITRE DE LA TÂCHE and choisir aujourd'hui fields, and only 2 records?

Also, have you tried evaluating your expression in the Data Viewer and seeing if there is any error message?
(With a field name like choisir aujourd'hui there is a good chance of the name being misspelled - but I don't like to guess).

 

[curious99]

Here is the file, it's a file created way back then (Filemaker II !!!).

 

Thanks once more for your help and insight.

 

Cheers

test_agenda_fmforum_dec2023.fmp12

[comment]

I am not able to reproduce the problem. I even changed the table's name to Agenda_ and it makes no difference.

See if this works for you on your system.

test_agenda_fmforum_dec2023+.fmp12

 

P.S. Please update your profile to reflect your version and OS.

Edited December 9, 2023 by comment

[curious99]

Hi, thanks for the quick answer back and the file you sent me and comments.

 

I ran the script you created (if you can explain me how you do "custom dialog options", I'd be grateful as I never did that) and it does run fine on my pc.

So I updated my profile. I also have a "server" on 15 pro that hosts this file : that might play a role actually but I would not know for sure. Probably you know if it is the case.

 

So on 16 pro adv. run locally, your file runs fine. However, when I import your script into my hosted Agenda_, it gives me an ? on data viewer as you can see :

Tomorrow I will try running it locally (we have a major storm coming our way so I need to finish some work outdoors right now) but if you think that the 'server" running 15 pro could be the culprit, let me know. At this moment, that is what I think could be the issue.

 

Cheers

[comment]

2 hours ago, curious99 said:

if you think that the 'server" running 15 pro could be the culprit

I doubt that. I suspect there is a difference between the files. Are you sure the file you posted is a trimmed copy of the actual file? I have a feeling it is not - which would defeat the entire purpose of making it.

But you will know for sure when you try unhosting your file (or hosting mine).

 

[curious99]

Eureka !

 

It works : when trying out the solutions locally, having the same issue, I decided to "recover" the file and although the summary showed not issue with the file, it now works perfectly. That process obviously rebuilt the index. Why would the recovery summary mention no issue even though something improved as per the script now working?

 

Also, you mentioned "Are you sure the file you posted is a trimmed copy of the actual file?" : I have never done that and do not know how to do that hence me not doing what you asked for. Could you let me know a bit more about that process ?

 

In any case, thanks once more for your time to guide me thru : please know I am sincerely grateful. for all your help in all these years.

 

Cheers

[comment]

The recovery process is black magic and we peasants know very little about it. I am glad it worked for you. Though I would be cautious about using a recovered file in production. This is (thankfully) not out of my own experience, just from reading warnings posted by others. And it may have become more robust in recent years.

1 hour ago, curious99 said:

Also, you mentioned "Are you sure the file you posted is a trimmed copy of the actual file?" : I have never done that and do not know how to do that hence me not doing what you asked for. Could you let me know a bit more about that process ?

It's very simple: you save a copy of the problematic file and delete from it everything that's not relevant to the problem. This is supposedly meant to save the troubleshooter's time - however, in a very large number of the cases the problem is identified during this process.

 

[curious99]

Got it, thanks for the explanation (copy question).

 

Now I have another issue with the same script : when the script prints, all is fine but it will only print page 1. Even if I select print page 1 to 2 for example it will print 2 pages but with the content of page 1. In fact, I have 71 records that I want printed thru that variable we created last spring. So if I allow the printing to be "all records" this scripts will print 71 pages of the content of the first page. Right now I print with the 81/2x11" and I do believe if I were to print using the 81/2x14" it would print more of the records but repeat that 71 times. I'll work on that but just want to share that issue and if you think of something, please let me know.

Thanks again,

Cheers

 

 

[comment]

9 hours ago, curious99 said:

In fact, I have 71 records that I want printed

But you do NOT want to print 71 records. You want to print a single field that contains data collected from 71 records. For this you need 2 things:

1. A layout with a single field stretched out to be tall enough to accommodate the maximum expected length of text (over several pages) and set to shrink when printed; 
2. A found set of 1 record.  Select 'Current record' in the print options.

 

Edited December 12, 2023 by comment

[curious99]

Bingo, that should do it, thanks once more,

 

All the best to you

[comment]

3 hours ago, comment said:

2. A found set of 1 record.

Actually, you don't need even that since you can select 'Current record' in the print options.

 

[curious99]

Eureka !

 

It works : when trying out the solutions locally, having the same issue, I decided to "recover" the file and although the summary showed not issue with the file, it now works perfectly. That process obviously rebuilt the index. Why would the recovery summary mention no issue even though something improved as per the script now working?

 

Also, you mentioned "Are you sure the file you posted is a trimmed copy of the actual file?" : I have never done that and do not know how to do that hence me not doing what you asked for. Could you let me know a bit more about that process ?

 

In any case, thanks once more for your time to guide me thru : please know I am sincerely grateful. for all your help in all these years.

 

Cheers

Makes perfect sense, thanks once more.

 

Have a Divine day

 

Cheers

Sorry, something happened that I do not understand, one of my previous answer merged with my last. In any case, I am very happy :-))