Script for incrementing a counter

[John Chamberlain]

I need to write a script that will examine a field (A) in a set of records. The desired result is to increment a counter (B) based on the contents of A.

A is always either empty or 1.

I need a script that will examine the contents of A and increase the contents of B by 1 (if A = 1), or decrease B (if A is empty)

There will always be 5 records to be examined, thus the result of the calculation will be somewhere in the range of +5 to -5.

Please forgive an old Fortran programmer, but I just can't seem to find the answer in any of the FMP books I have.

Any help will be greatly appreciated,

[siroos12]

Hi John,

 

Does this work for you?

 

Go to Record/Request/Page[ First ]
Set Variable [ $B; Value:0 ]
Loop
If [ Products::___About_this_table = 1 ]
Set Variable [ $B; Value:$B+1 ]
Else
Set Variable [ $B; Value:$B-1 ]
End If

Go to Record/Request/Page[ Next; Exit after last ]
End Loop

 

You may also achieve the same results with calculation:

 

Sum(YourTable::FieldA) - ( 5 - Sum(YourTable::FieldA))

Edited May 24, 2018 by siroos12
Missed a step in sample script

[Fitch]

If I understand correctly, when all 5 records are blank, the result is -5, so let's start there.

If one record is 1, we'd add 1, but we'd also be not subtracting 1, so really we're adding 2 for each positive. So:

2 * Sum(A) - 5

The method of deriving the Sum (or Count) of A could be done in various ways, so let us know if you need help with that. E.g. it could be scripted as Siroos proposed.

[John Chamberlain]

Sorry Fitch, but that only works if every client has exactly five entries in the date range. Because we have clients dropping out, and new clients joining all the time, there will likely be anywhere of 1 to 5 entries per client.

I have banged my head against the wall for several days now, so I am appealing for help again. I have stripped down the file to just the essentials, and it is attached.

The script labeled xxx is what I have so far. I put in a summary field (is there any way to utilize that in a script or on a layout?)

I am getting desperate, so any help will be greatly appreciated.

TEST.fmp12.zip

[bruceR]

Help will be impossible until you specify user name and password for your TEST file.

Edited June 4, 2018 by BruceR

[John Chamberlain]

Sorry, the name is John and I set it for no password

 

[NLR]

Take a look at my revision.  I basically just redefined the field MISSED PICKUP QUANTITY•••

I've left comments within the definition explaining that because this field needs to calculate when the other field it relies on is empty, you must remove the tick which says "Do not evaluate if referenced field is empty"  Also I changed the definition to read:

If ( IsEmpty (PICKUP QUANTITY) ; -1; 1 ) 

Other than that I adjusted your script a little.  Because you have defined that summary field, there's actually no need to run the bulk of your script because once it's sorted, the result you seek is shown.

Ralph

 

TEST.fmp12.zip

[John Chamberlain]

OK. displaying my ignorance here... I have always thought that summary fields could not be used in equations.

The whole object of my effort here is to establish a percentage of pickups mad for each individual in the  Client Data table. So, in the sample file here the ultimate result for Jackie Batch would be 1 (the number of pickups missed) over 5 (the number of pickup dates in the sample (20%); while the result for Irma Cortez would be 4 over 5 (80%).  I tried a calculation using the summary field, but the result is confusing (tied placing in in both the detail line and the summary line).

Canou tell me how to get where I need to be?

I have further stripped out the extraneous fields, and a copy of the file is attached.

Again, Name is John, Password is null.

 

TEST.fmp12.zip

[Fitch]

On 6/3/2018 at 4:33 PM, John Chamberlain said:

Sorry Fitch, but that only works if every client has exactly five entries in the date range.

"There will always be 5 records to be examined" is what you said earlier.

"The whole object of my effort here is to establish a percentage" is NOT what you said originally. 

Not saying that to be critical -- it would have helped to know that earlier, but I know it's not always obvious how to describe a problem. I'll look at your file if I have time, unless someone else gets to it first.

[John Chamberlain]

What I should have said is that there would always be 5 to 9 records to be evaluated. The date range is based on a calculation of the current date selected minus 63 days. Thus there could be as many as 9 weeks or as few as 5 to be evaluated. However, there could also be a record count of less than 5, Once I had the equation working, I had planned on setting a null field for any total of records of 1 to 4.

Please accept my apology.

[NLR]

21 hours ago, John Chamberlain said:

I tried a calculation using the summary field, but the result is confusing

Try this:  To use a sub-summary value, consider the  GetSummary function

 

[John Chamberlain]

 

Thanks. I tried this and while the sub-summary values are correct the GetSummary values are for the entire number of records. I have attached the modified file to show this.  I know I am missing something here but my addled brain can't seem to figure this out.

I truly am sad to be so stupid...this is what comes from being self-taught and 88 years old to boot.

Again, any help will be greatly appreciated.

TEST.fmp12.zip

[NLR]

The summary approach isn't the only way.  I also like a looping method like one shown earlier by someone else.  I must admit I found it difficult to grasp what your intentions are, however on a few more read-throughs of your various letters I think I get the idea...

Anyway, I've put together a script which runs a loop. And within this loop there's a "Find" which gets records for each Client for the 63 day range you decribed (ie. going back 9 weeks from "today").  Then a second smaller loop within, which counts up each occasion a Pickup is done by that person.

So for each person, we know the number of days involved as well as the number of pickups that person did.  This leads us into deriving a percentage result for each.

Hope this is closer to what you're after.

Regards
Ralph

TEST.fmp12.zip

[Fitch]

Attached is a solution using a relationship based on selected start and end dates. Related total calculations are included. I think you can do the math to find your percentage.

TEST.zip

[John Chamberlain]

First, let me thank you for. I truly do appreciate your help.

With regard to the file you sent yesterday, I must say that your script is way beyond my capacity to understand. Further, I feel that I somehow may have made this thing much more complicated that necessary.

I note that your latest script displays messages that have the correct percentages, even though the percentages displayed in the sub-summary section, once the script has run, are not the same and indeed seem to be independent of the numbers in the records being evaluated (30.95% in both cases even though the number of pickups and the number of dates are not the same and 30.95% is the result of all of the records in both names).

 

If it will help, I am attaching a copy of the complete FMP file (Name: John, P/W null), and a description of the things the file is supposed to do.

The  program is designed primarily to contain the names of persons who are recipients of food packages (Boxes and /or Bags of locally donated foodstuffs). The clients are classified as WEEKLY, EMERGENCY, HOME DELIVERY or WITHDRAWN.

The problem I am trying to solve relates only to those clients who are classified as WEEKLY.

Distributions are made to the WEEKLY clients on a singled day assignment (Monday through Saturday). Distribution Sheets are generated by going to the DAILY SHEET PREP page (Command 3), then choosing a date. Clicking on the PRINT A DAILY GRID SHEET) invokes a script that find the proper records, goes to the LINE BY LINE screen, and generates a percentage of pickups made figure for each person

The object was to find a percentage of those who had not made their scheduled pickups. Any client who fell below the 50% line would then be removed from their Weekly distribution spot (classification changed from Weekly to Withdrawn). The criteria was based on there percentage generated by the number of pickups made over 9 (the number of pickup days between the selected DAILY GRID SHEET date and the previous 9 weeks). Those with a 55.5% (5 out of 9) rating would be warned if they picked up, while those who passed below that (4 out of 9) would be placed in the Withdrawn category.

The script invoked by the PRINT A DAILY GRID SHEET script works, in that the proper records are selected, and the daily sheets are printed on time.

The problem is in evaluating the pickup percentage. What I need here is a number representing the number of pickups divided by the number total entries for a person. This number will be used to insert a warning on the DAILY GRID SHEET whenever that person is in danger of falling below a 50% pickup mark. The message would also have to be adjusted so that new persons entering the program would not get a warning until they had been in the system for three or more weeks,

None of this figures in the solution. Just having the number of pickup opportunities and the number of actual pickups is what I really need.

Is it possible that FMP can generate the sub-summary numbers, but cannot allow the results to be used in an individual equation?

 

 

 

 

 

 

HOY FIle 6-5 Copy.fmp12.zip

Edited June 9, 2018 by John Chamberlain
clarification