Script for Pulling Records based off Find?

[tussing515]

I currently have a script that pulls all US based off global dates and global center fields. It then omits certain results based off of the outcome "LTC".

 

Is it possible once the report has pulled it's information to say "Ok now that you have these results, do a search for any other records including their Client number and case number?"

 

Here is my current script in case you need it:

 

 

Go to Layout [ “Test Report” (Client_Activity) ]

Enter Find Mode [ ]

Set Field [ Client_Activity::Center ; GlobalFields::GlobalCenter ]

Set Field [ Client_Activity::Visit Reason ; "Ultrasound" ]

Set Field [ Client_Activity::Visit Date ; Client_Activity::GlobalStartDate & "..." & Client_Activity::GlobalEndDate ]

Perform Find [ ]

Sort Records [ Keep records in sorted order ; Specified Sort Order: Clients::Last Name ; ascending ]

[ Restore; No dialog ]

Constrain Found Set [ Specified Find Requests: Omit Records ; Criteria: Client_Activity::US Exit Intentions : “"Carry"” ]

[ Restore ]

[LaRetta]

I may not understand the problem but, with your existing found set from script above, you might consider just adding the following pseudo steps right to the end:

Enter Find Mode [ uncheck pause ]

Show Custom Dialog [ "Include other records now with client and case"

... Here go to input tab on custom dialog and select ClientNumber field and CaseNumber field as input and label them ; Ok ; Skip ]

If [ Get ( LastMessageChoice ) = 2 // user selected skip so stop ]

Exit Script

Else If [ not Length ( ClientNumber&CaseNumber ) // both fields empty ]

Show Custom Dialog [ "No criteria entered ]

Exit Script

Else

Set Error Capture [ On ]

Extend found Set

End If

Added ... Notice I added the error capture above ...

[tussing515]

LaRetta - That kinda of does what I want but when you run that script is has you manually enter the ClientNumber and CaseNumber. What I would like it to do is automatically pull that from the clients already found in the previous steps of the script.

 

Does that make sense? Other wise it works.

[Tom R.]

I'm not an sql guru, so can't give you the exact steps, but you could use the ExecuteSQL function to "select distinct" values from your found set, store them in a variable, and then loop through and extend the found set based on the values in the variable. If you have client number and case number in separate fields, you would need to either concatenate them in a calculation field, or write your SQL statement to create a pseudo field and then use the select unique statement on the concatenated values. 

 

Sorry for such a vague reply; I'm still trying to learn all the nuances to using the Execute SQL function. I've found Beverly Voth's reference to be very helpful. http://www.filemakerhacks.com/?p=6406. See page 26 for description of the SELECT DISTINCT function. 

 

Good luck,

 

Tom

[LaRetta]

Okay then I will assume you start on a layout where both numbers are available? The idea is to grab the values then before you go. When you attach your script to a button, you will want to write the Client Number and Case Number to an optional script parameter similar to (and you can just double-click the fields to insert into the parameter):

SourceTable::ClientNumber & ¶ & SourceTable::CaseNumber

And then script would be adjusted to:

# Next step takes the variable from your button and uses it here in its list state.

Set Variable [ $p ; Get ( ScriptParameter ) ]

# client number is first value and case number is second

Enter Find Mode [ uncheck pause ]

Set Field [ ClientActivity::ClientNumber ; GetValue ( $p ; 1 ) ]

Set Field [ ClientActivity::CaseNumber ; GetValue ( $p ; 2 ) ]

Set Error Capture [ On ]

Extend found Set

In FileMaker, perspective is everything and I do not see where you indicate the table name where this script originates but that is where the button should be with your script attached (even if it's the same table Client_Activity. here the button should have the script parameter attached. If the Client and Case come from another table than where you start then you will need to use them instead.

[tussing515]

LaRetta - I've been struggling for the past few weeks trying to get this to work and think my novice skills are hindering me. The layout starts out blank then I enter this information and hit Run Report.

 

It then goes to Table - Client_Activity - and does the rest of the script. For some reason when I attached the rest of your script to my current one it doesn't do anything. There are only two tables: Clients and Client_Activity. I think I'm just having trouble understanding what to do next. This is what the script looks like now though.

 

 

Go to Layout [ “Test Report” (Client_Activity) ]

Enter Find Mode [ ]

Set Field [ Client_Activity::Center ; GlobalFields::GlobalCenter ]

Set Field [ Client_Activity::Visit Reason ; "Ultrasound" ]

Set Field [ Client_Activity::Visit Date ; Client_Activity::GlobalStartDate & "..." & Client_Activity::GlobalEndDate ]

Perform Find [ ]

Sort Records [ Keep records in sorted order ; Specified Sort Order: Clients::Last Name ; ascending ]

[ Restore; No dialog ]

Constrain Found Set [ Specified Find Requests: Omit Records ; Criteria: Client_Activity::US Exit Intentions : “"Carry"” ]

[ Restore ]

Enter Find Mode [ ]

Set Field [ Client_Activity::Ct Number; GetValue ($p ; 1) ]

Set Field [ Client_Activity::Case; GetValue ($p ; 2) ]

Set Error Capture [ On ]

Extend Found Set [ ]

[LaRetta]

And are you sure that you added the script parameter?  Also I do not show this line in your script where you take the script parameter and set the two fields CT Number and Case which was the line as:

 

Set Variable [ $p ; Get ( ScriptParameter ) ]

[tussing515]

Well I'm not sure I exactly understand how to add a script parameter but that line is there. it looks like this:
 
 
[ Restore ]
Set Variable [$p; Value:Get ( Script Parameter )
Enter Find Mode [ ]
Set Field [ Client_Activity::Ct Number; GetValue ($p ; 1) ]
Set Field [ Client_Activity::Case; GetValue ($p ; 2) ]
Set Error Capture [ On ]
Extend Found Set [ ]

[Rick Whitelaw]

You need to write the script that does what you want, then call it with the PerformScript script step. You'll be presented with the Optional Script Parameter. Oh, and you "call" the script with another script whose only step is PerformScript.

[LaRetta]

As Rick suggests, make sure you've added the script parameter to the button as described in post #5 here:

"When you attach your script to a button, you will want to write the Client Number and Case Number to an optional script parameter similar to (and you can just double-click the fields to insert into the parameter):

SourceTable::ClientNumber & ¶ & SourceTable::CaseNumber"

If you still are stuck, you can zip and attach your file and we will be happy to help you with it.

[tussing515]

LaRetta - I'm really showing my lack of skills here, how do I save the file to a zip? I know how to attach here obviously but i'm not sure how to save it.

[LaRetta]

You should be able to select your file, right-click and 'Send To' and Compressed. 

[Rick Whitelaw]

Tussling515,

Just noticed your profile. Are you sure this is correct? A person with certification for 12 ought to know about Script Parameters, no? Not trying to be a jerk, just saying . . .

[tussing515]

LaRetta - Sorry, I should have specified better there. I know how to make a file a zip, I'm not sure how to save my script as a file. Our database is on a remote server, plus do to confidentiality I can't send the whole thing over. I'm just unclear on the saving the script step.

 

Rick - Sorry, it was a mistake on my part when I first set up the profile. I checked 12 because that was the FM platform I was running. I changed it. I'm still new to all this but I appreciate everyone's help.

[Rick Whitelaw]

In Manage Scripts, select the script, select Print and then save it as a PDF. Then zip the PDF. That's me coming from a Mac perspective. I think you can do the same on Windows, but I'm not sure.

Rick.

[tussing515]

Ok so I attached both scripts. The "Test Report Find" is my script that does the initial search and the "Test Report w_Parameter" is where I tried to do what you were suggesting. Let me know if this doesn't work or if you need more data from me.

Test Report Find.zip

Test Report w_Parameter.zip

[LaRetta]

I'm not sure why you placed it as another script but you don't even have it fired as a sub-script.  The script parameter is attached to the FIRST script, right?  The second script won't see it!  Script parameters die with their script unless they are passed on to a sub-script which you haven't done.

 

Here is what I suggest ... create a new file with only the key fields and relationship (Clients, Client_Activity, GlobalFields).  Create a few dummy records (only enough that you can clearly see what works and what doesn't) and so you can clearly see the results of the find.  Then create ONE script, just adding the lines I gave you to the end of the first as I originally indicated. 

 

I started to create the file for you but truly I do not have the time right now.  Once we produce the exact desired results, translating it into your file will be piece of cake. 

 

Oh, I also highly suggest that you purchase FMPA (the Advanced version) so you can use debugger to step through scripts and see what is happening.

Edited April 20, 2013 by LaRetta

[tussing515]

The first file was just my original script then the second file was my original script plus your extra lines. Should my script include even the previous scripts you gave me? Sorry, I'm confused on that. Is there anyway you could show me what it should look like all spelled out?

My company doesn't have FM Advanced but I would like to get it.

[LaRetta]

Your second script should do it. You need to create test file like I suggested even if it isn't perfect and I'll help you from there. :-)

[tussing515]

I did like you said, created a couple test files then made the first half of the script, which I already knew worked and then started to add in the second one line by line.

 

 

Go to Layout [ “Test Report” (Client_Activity) ]

Enter Find Mode [ ]

Set Field [ Client_Activity::Center ; GlobalFields::GlobalCenter ]

Set Field [ Client_Activity::Visit Reason ; "Ultrasound" ]

Set Field [ Client_Activity::Visit Date ; Client_Activity::GlobalStartDate & "..." & Client_Activity::GlobalEndDate ]

Perform Find [ ]

Sort Records [ Keep records in sorted order ; Specified Sort Order: Clients::Last Name ; ascending ]

[ Restore; No dialog ]

Constrain Found Set [ Specified Find Requests: Omit Records ; Criteria: Client_Activity::US Exit Intentions : “"Carry"” ]

[ Restore ] #Everything up until here works fine.

Set Variable [ $p ; Get ( ScriptParameter ) ] #I didn't notice any change here. No working or not working signs.
Enter Find Mode [ uncheck pause ] #This worked fine.
Set Field [ ClientActivity::ClientNumber ; GetValue ( $p ; 1 ) ] #It stopped working here.

 

I'm not super clear on where it's supposed to get the ClientNumber from and I think it's confused too.

[Rick Whitelaw]

What is the Script Parameter set in script 1?

Also why use GetValue? The variable ?p is fine by itself.

[LaRetta]

I asked that you attach the FM test file.

[tussing515]

Sorry LaRetta. How do I save the test file? My FileMaker is hosted on the Server so I don't have a "Save As" option and when I print to a pdf, it's blank. I've heard it's because we have tabs that it doesn't print correctly but that's besides the point. How do I attach the test file?

[tussing515]

Rick- To be honest I'm not sure. I'm sorry. I just blindly following here.

 

LaRetta- I figured out how to save them I believe. Let me know if this doesn't work well. Do you also need the report or no?

Test-1.zip

Test-2.zip

Test-3.zip

[bruceR]

There are no scripts in those files. So no, it doesn't appear you've figured out how to get to the actual files. Which you will have to pull from backups off the server.

[tussing515]

I've listed the scripts already and attached them earlier. These are the test client files not the scripts.

[bruceR]

So let's be clear: you want US to create and edit the scripts in your files?

You're asking for free help.

 

Adding the scripts is YOUR job; and should have been done in by about post #3; not post #27.

 

Post the files with scripts included. 

[tussing515]

BruceR - I'm not trying to cause any trouble here. Yes, I'm asking for free help because this is a forum for helping people. I've said that I'm new to FileMaker and struggling. I've tried scripts and it's just not working for me so people here have graciously offered to help figure out what's going wrong. I've previously attached my script if you look back and I've now attached some test clients.

 

To make it easier though, here are my 3 test clients AND the script that is having issues.

Test Report w_Parameter.zip

Test-1.zip

Test-2.zip

Test-3.zip

[LaRetta]

Sorry but I cannot help you.  You have given me three FM files without a script and a script printed out as pdf.   Your script references a Client Activity date but there is no such date in the files.

 

Why are you not creating the script in one of the sample files and trying to get the result you want ... then tell us exactly what you should be getting that you are NOT getting?  Why is it so difficult for you to create the script in one of these three files and why are you giving us three files anyway?

 

Your repeated insistence that you have already given us what we need is not the case at all.  I asked for you to create a simple file showing your script and some data and what records you should get that you are not getting.

 

I give up.

[LaRetta]

"I've said that I'm new to FileMaker and struggling. I've tried scripts and it's just not working for me so people here have graciously offered to help figure out what's going wrong."

 

I will try one more time.  Please create your script in one of the files - replicating what you THINK it should be even if it produces wrong results.  Then tell us what results you SHOULD get from the records included, as I asked you to do originally.  If you do that then we can determine if you have something wrong in the script (or whether you are forgetting to include script parameter) or whether the script itself needs to be adjusted to produce the record record set that you want to find.

 

We have nothing to work with at present.

[bruceR]

"BruceR - I'm not trying to cause any trouble here. Yes, I'm asking for free help because this is a forum for helping people."

 

You're missing the point.

 

If you were paying for help, I presume you would put in significant effort into preparing your files and communicating clearly so that the experts you paid could work efficiently for you.

 

But since you are not paying for our time, you are acting as though our time is LESS valuable. You are making a deliberate trade.

You are saying, in effect, that since the help you are getting is free, you will put in LESS effort on your side - thus requiring MORE effort on our side, MORE effort from helpers.

 

The DEAL for free help is this: YOU DO THE TIME CONSUMING WORK. YOU MAKE IT **AS EASY AS POSSIBLE** FOR HELPERS TO HELP YOU.

 

We are now at 30+ messages, trying to get you to cooperate effectively to solve a problem that could have been solved in five messages.

[LaRetta]

I feel that tussing515 (I would love to be able to call you by your first name, tussing515, really) is simply unaware, Bruce, but if you are correct then you answered it very well.

 

Hi Tussing515, when we ask specific questions, it is because we need those answers to properly assist.  I do not know if you are missing putting a script parameter in your script, whether you are referencing an incorrect table occurrence in your find or whether you are simply not getting the found set that you expect.  I have repeatedly asked for a file with the critical fields and your script attempt that fails (with a handful of records) so we can spot the issue IMMEDIATELY.  As is, we go around and around and it is frustrating to us as well as it must be frustrating to you.

 

You can put together an example in 10 minutes showing the relationship based upon their keys and the fields you are searching.  Again I ask that you take one of your test files and put your script (as you translate it to YOUR fields in YOUR file) so we can see what needs to be fixed.

[Rick Whitelaw]

Bruce and LaRetta,

You've both delivered the message clearly. At this point there really is no point in going further. Tussling either doesn't understand (which I doubt) or simply refuses to supply the information you need to be of help. It's time for Tussing to read the manual.

[tussing515]

Bruce and LaRetta and Rick, I truly am sorry if you feel I am wasting your time or considering it less value. I'm not. I'm just very new to FileMaker and am apparently struggling to understand what is needed from me and how to even get it to you.

 

LaRetta- I do thank you for the attempted help but I apparently just don't know what to give you or how to get it in a format you need. Thank you again for your attempted help.

[mr_vodka]

Just zip your file and attach it here. Use the "More reply options" button to attach.