I have a weekly report I sent to a client with errors on his system (not FIleMaker errors) and how many weeks they've gone unresolved.
In FileMaker I export a report and save as PDF and attach it to the weekly email I send them. The report looks really nice as you would expect, but I know they don't open the attachments. (sigh!)
So, I have two fields:
- Error information
- Number of weeks error has been present (unresolved).
What I'd like to send them would be text like this in the body of the email so it's more difficult for them to miss (ignore):
Error1 text (x weeks)
Error2 text (x weeks)
Error text (x weeks)
.
.
.
Now, sure, I could do a micro-service call, but this case seems like it would be better to just do in FileMaker. The question is how best to get this format or very close to it.
Is this a case where I'd be better off creating a text file using the new FMP file functions so I can better control the format of what gets created?
I could probably do this with a report, but I really just want the text. And, if I export the records to a "mer", for example, the fields are quoted and I have some other clean up to do.
Maybe there's a better way in FileMaker I don't know.
Suggestions?
Thanks!
1 Like
hm, without knowing the FM version and a sample line - maybe error text spanning across more then one line/row - and the source of the list - is it a table or text in a field - it is wild guessing 
what I tend to is building a loop either in script or with MBS.Loop / NthRecord and collect data in a variable that gets filled with a header and some info in kind of footer
now do as you wish, in the old days I wrote it to a global text field and singled that out in a layout only for this purpose to get exported via Export Field Content into a text file.
Today I would write it to disk via MBS, you could add it to your mail body...
1 Like
That's interesting. I had not considered actually sending the email from FileMaker. I was just creating a text file to include in the email body in Thunderbird.
I'm using FMP 16 and I may yet upgrade to get the file functions. Writing micro-service methods isn't always the right approach.
Thanks for your insightful reply. I will reconsider how I do the emails and possibly use FileMaker to send the email also.
There are 2 options to get the data easily, that I would consider.
- Summary field ( defined as ListOf ), that pulls in the value of a stored calculation. So you would find the records you want, and then just grab the value of that field.
- Virtual List - find the records you want, then just loop and stuff them into a variable.
How many "errors" are you grabbing? If it's less than 5-10k, either method is fine.
1 Like
Thanks...the second options was perfect. I just looped in a script and created the variable (I didn't see a "pilcrow" item in the script window so I added that manually for vertical spacing between records. Then I went back to the main layout and did a SET FIELD with the variable's value.
One question though. I added a Freeze Window before I changed layouts, but the screen still flashes and visually switches layouts. Is there a better way to suppress that?
Thanks J.
Depends on what the script is doing. There are several steps that force a redraw of the window.
Using something like:
List ( $var ; newValue )
Will make an easy way to generate the list as you loop also.
...if you are on macOS the "pilcrow"-shortcut is alt-3
but the List-function is way more convinient
Really? On my mac it is option-7.
1 Like
That's CF only though, right? I was augmenting an existing script but still good info to know. Thanks.
+1 to the List() approach. It's my favorite - I really like its elegance.
If I need to build up a huge amount of text, I might opt do something else, but otherwise List() is my go-to.
Regarding window flashing:
Totally agree that it depends on what the script is doing. Just guessing at the case being worked out here, I would think that it should be possible to get the script to work without any window flashing.
Below are a few things that I might consider when harvesting data across a number of records from a single table. Some of them can help prevent window-flash:
- Reducing layout changes: If all of the data is coming from one table, then I would imagine that just a maximum of two layout changes would be needed: one to get to the context of the table with the error fields, and one layout change to get back to the original context after all of the data collection has happened.
- Gotcha's that I might look out for would include any script trigger that fires due to a layout ore record change, or the use of a layout in list view, or a layout with a lot of content to draw. My MO is to tend to have dedicated blank form view layouts (with no script triggers attached) that I use when I need to script these "found-set" types of operations.
- Once at the target context, iterating to harvest values from the found set does not necessarily mean having to repeatedly use the GoToRecord script step.
- Instead: oftentimes with the use of the GetNthRecord function, it is possible to stay put on one single record and still be able to harvest all of the needed values from the found set.
- The caution with GetNthRecord is that it is great for getting values from records in the current context, but if related data is involved, e.g. "For each record, get me values from field X and related field Y", then GetNthRecord is probably not the correct tool.
Hope this helps. The above is certainly not an exhaustive list of considerations, but it's some of the more common ones.
Kind regards & sincerely,
-steve
3 Likes
Wow, thanks!
The help for the List function says {field ; {field....}} to that effect. So, how would you use the List function if you wanted to also include some free text? Does that work?
I solved it by just looping and creating a variable that was the error text and an "---→" pointer and (number of weeks) value.
Example: error 1 --→ (3 weeks)
Thanks Steve.
Something like this would work:
List ( $list ; errorField & “ —-> “ & weeksField )
In some circumstances List won’t like that. So do a Set Variable with content of that second parameter, and then put the variable in second parameter.
Set Variable [ $currentValue ; errorField & “ —-> “ & weeksField ]
Set Variable [ $list ; List ( $list ; $currentValue ) ]
3 Likes
Excellent, thank you.
And @harvest got me thinking about the whole email workflow I was doing. So rather than just use List or whatever to create the text in the field with the error info and manually copy that into a separate email program, I'm doing this:
-
Create the PDF to attach on the desktop (the nice FMP report with good colors and display).
-
Create the list information and update the FMP field back on the main layout.
-
Do the actual email combining both the PDF as an attachment and some boilerplate text + the list just created of errors and time error has been unresolved.
In my testing, it has worked every time. The file always gets generated in time and is attached to the email.
So, thanks to @harvest for making me re-think that whole flow a bit. I added my boilerplate text to a global field. Added the calculated "notes" generated (as this thread discusses) and now just email the whole shebang from FMP. Will probably save me some minutes each week and some frustration. Definitely worth it. Automate what's possible.
Thanks again to all.
2 Likes
Hi @anon45965781,
One last goodie for the road:
ExecuteSQL would be another tool for generating the result.
A calculation (or similar variation) like this might work:
Let([
~select = "SELECT ErrorCode, NumberOfWeeks FROM ErrorTable";
~where = "WHERE _________________";
~sql = List( ~select ; ~where )
];
ExecuteSQL( ~sql ; " --> " ; "" )
)
One caveat about the SQL approach is that I have glossed over the best practice of keeping the execution robust against breaking if schema names change. It is not difficult to patch that up, but in doing so, IMO, the code often loses both simplicity and elegance. Still -- it's nice to have this type of option as one more item in the bag of tricks. An advantage of the ExecuteSQL approach is that there should be no need to change layouts to get at the data.
Sincerely,
-steve
alt-3 gives me £ (pound sterling)
option-8 gives me • (bullet)
option-7 gives me ¶ (pilcrow)
Great solutions here. I prefer scripting and handling resulted data somehow because that does not need changes to data structure. It can be virtual list or something else. Virtual lists has quite a lot of restrictions. But exporting is possible so if that is needed then it’s way to go. If data needs to be shown I tend use web viewer with some html-js solution and feed the data as json there. In multi window apps global fields has issues.
If there are lots of field in the layout which script loops, it might be slower than layout with only necessary fields. With larger data sets I create a specific layout just for scripting.
For example I needed a json array generated from records’ layout field data. Looping hundreds of records were significantly faster when having only necessary fields. In FMP 19 I can do also local data api query (without server) which (i guess) is fast and returns data as json.
so sorry, should have written: on my macOS german keyboard layout, but lazy as I am...
For me it's OPT + 7 also = pilcrow.
