Tally unique records

[Greg Hains]

Hi.

I am just discovering ExecuteSQL and how it can help with powerful calculation fields, but am struggling with something. Could somebody please point me in the right direction?
To add to this, I only started to haver this problem this morning shortly after upgrading my Mac copy of FMPA to v14 - but think the problem is me and not the machine or FMPA.

Attached is a sample file of what I'm trying to achieve.

For example: In this list I have a number of names, each with a score. Let's say I want to find out how many individuals got a TOTAL score of between 20 and 30 and have the "Unique" field display that. It may be the result of 1 score or 10 scores, but the total sum is what I'm checking. I can see my formula is not quite right here as the Total field adds them all up, and not just that individuals, so can I do this in a single table?

Greg

TestSQL.fmp12

[LaRetta]

Hi Greg,

ExecuteSQL() is certainly a great tool but I never use it in field calculations.   I suggest instead that you use relational structure which means a People table and a Scores table.  Then from your People table you can use calculation of Sum().  Even better ... create a summary report in Scores which groups by Name (but still create the People table).

I know this is just a sample file but I hope you also know about using unique IDs in all your tables even if the table is not used for relationship keys to other tables.

If you would like an example of either of the suggestions in my first paragraph, I would be happy to do so.  :-)

Edited June 18, 2015 by LaRetta

[eos]

I can see my formula is not quite right here as the Total field adds them all up, and not just that individuals, so can I do this in a single table?

A summary field gives you summarized values if your found set is sorted, AND 

• you place the summary field into a sub-summary part whose break field is in the sort order, or
• you use GetSummary ( summaryField ; sortField ) to get the value of the summary field for the current record

btw, try this:

ValueCount ( 
  ExecuteSQL ( "
    SELECT COUNT ( Name )
    FROM TestSQL 
    GROUP BY Name
    HAVING ( SUM ( Score ) > 10 AND Sum ( Score ) < 18 )
    " ; "" ; ""
  )
)

Edited June 18, 2015 by eos

[Greg Hains]

Hi Laretta,

I think I misrepresented my question by oversimplifying the problem (and supplied example) I was having. I thought my example my demonstrate what I was trying to do.

My actual FM solution is based on management of clients and their in-house clinical attendance. The two main tables are CLIENTS and EPISODES, where episodes are the attendances. The two tables are joined by a UUID value ) called "_id_client".

The bare bones of each episode comprises of:

- Client's name: 
- Gender of the client
- Date they were admitted: DATEOPENED
- Date they were discharged: DATECLOSED
- Duration of the Episode: (DATECLOSED - DATEOPENED) + 1
- Total duration they were there: EPISODE_DURATION_TOTAL. Total of all each client's episode duration (Summary field) - which does work.
- Start date of reporting period: g_OSR_DATESTART
- End date of reporting period: g_OSR_DATEEND 

I was trying to sum the total time of any given client i.e. how many clients spent more than 3 weeks in the clinic - which could comprise of 1 or more episodes, broken down by gender, and within a certain period (e.g. financial) year.

The actual calculation field I used was the following, but it seems to ignore the Episode_Duration_Total parameter altogether. I remove it - same result, change any other parameter, and the results change accordingly and correctly.

ExecuteSQL ( "SELECT COUNT(DISTINCT ClientName)

  FROM Episodes 

  WHERE Gender = ‘Male' 

  AND (Episode_Duration_Total > 21)

  AND ((DateOpened BETWEEN g_OSR_DateStart AND g_OSR_DateEnd) OR (DateClosed BETWEEN g_OSR_DateStart AND g_OSR_DateEnd))"

; "" ; "")

 

What can you see me doing wrong here?  I know it's my technique and lack of knowledge here, so appreciate your help in resolving it.

Many thanks.

Greg  

[LaRetta]

What can you see me doing wrong here?  

Using ExecuteSQL() instead of a relationship? 

Hey, each time this resulting value is displayed, it must recalculate for EVERY client in your table if the record is open.  It is not the best choice when you already have a valid relationship based upon id_client of which you can take advantage.

In Episodes, you should drop the client name and use the id_client.  You can always gather the client name and gender through the valid relationship you've already established; that's the purpose of relational design.

As for the global g_OSR_DATESTART and g_OSR_DATEEND, I'm not sure how they apply - are they the client admit and discharge dates in Episodes?  Anyway, I really think it can be simpler and faster  if you consider letting the relationship do (at least most) of the filtering for you.

Added:  Your summary in Episodes can be used for sure.  And don't forget that, while standing in a Client layout, you can search your Episodes table directly for the dates you wish to report on which would find Clients within that timeframe.

And while true that the summary will not respond filter the relationship according to the dates, you said you want the entire duration of a client's episode so I think it would work in this case. 

Edited June 19, 2015 by LaRetta

[Greg Hains]

Hi LaRetta,

Thanks for this - I'll look into this as soon as I get home. (developer by night, engineer by day)

I've not forgotten your input either Eos - thankyou.

I'm taking all this info in to better my skillset  

Cheers,

Greg

[LaRetta]

I'd just like to see you end up with a solution which doesn't run like a dog and sink like a stone, Greg.  With FM, there are many roads to Rome ... some straight-line fast and some around the globe the opposite direction.  All lead there eventually but at what cost?  

Some say, "it's only ten seconds more to run" and ten seconds seems like such a small time period until you are the User sitting there waiting for the solution to do something.  Ten seconds is an eternity; pretend you've clicked a button and count out the time and by the end, you'll hate the program.  By the time you hit 5 seconds, you're ready to quit.  Heck, you're an engineer ... I doubt I need to convince YOU!

ExecuteSQL() has MANY advantages but, in most instances, using it in a calculation isn't one of them.

[Greg Hains]

Thanks LaRetta,

In your earlier question, g_OSR_DATESTART and g_OSR_DATEEND were global fields populated by the end user for the scope of the report. They could still be used in relationship filtering anyway - as opposed to ExecuteSQL() - yes.

I understand that whilst standing in a layout for Clients that I can access Episode data to tally total episode duration, and if there are no duplicate clients then I should be able to access the information from there, meaning the stats I pull from episodes would need to be done from Clients layout and not Episodes at all. Did I get understand you correctly here?

I appreciate your feedback (I have much to learn and value this help).
I don't want to upset the balance of things here, in terms of the support offered by yourself and others, however, I do feel like I'm freeloading as I have little to offer back - at my current skill level. I am prepared to pay for quick snippets of help - rather than feel guilty of using up people's time.
Maybe this last post should go into a Counselling forum on this site. lol. Thank you moderators. 

Once again. cheers.  

Greg

 

[LaRetta]

We ALL owe a lot to this forum and the help we've received and/or the things we've learned because of it.  I suggest that folks be willing to thank Ocean West ...

site donation

... because of him, we ALL benefit greatly.

Don't worry about giving back right now.  Some day you will.  The day I discovered this forum is the day I began using FileMaker (although I had been a DMS for 10 years prior).  I have never left.

Edited June 19, 2015 by LaRetta