fmp17 Relationship for finding earliest date a given goal is achieved

[alanf]

I have a table named Sales with the following data:

----------

ID

DATE

UNITS

-----------

Based on Sample data below, i am trying to query the table by creating a relationship to find the ID that achieved a specified Unit amount on the earliest date.

In other words, and for example, which ID achieved a specified Unit amount first (earliest date)

Specified Unit Amt of 5 = 1 Expected records returned result

301

10/13/2018

6.5

Specified Unit Amt of 2 = 2 Expected records returned result

766	10/03/2018	2
360	10/03/2018	2

 

I think a selfjoin may be required.  Any thoughts appreciated!

 

Thanks

Alan

 

SAMPLE DATA

                                   ID                          DATE                                     UNITS

301	10/15/2018	9
301	10/14/2018	8.5
301	10/13/2018	6.5
360	10/14/2018	5.5
305	10/15/2018	5.5
301	10/12/2018	4.5
656	10/13/2018	4.5
360	10/13/2018	4.5
305	10/14/2018	4.5
611	10/14/2018	4.5
305	10/13/2018	4
15	10/13/2018	4
301	10/11/2018	3.5
360	10/12/2018	3.5
611	10/13/2018	3.5
301	10/09/2018	3
15	10/11/2018	3
656	10/12/2018	3
611	10/12/2018	3
200	10/14/2018	3
301	10/06/2018	2.5
611	10/09/2018	2.5
200	10/13/2018	2.5
766	10/03/2018	2
360	10/03/2018	2
785	10/05/2018	2
301	10/05/2018	2
611	10/05/2018	2
439	10/10/2018	2
656	10/11/2018	2
510	10/15/2018	2
360	10/01/2018	1.5
510	10/04/2018	1.5
611	10/04/2018	1.5
305	10/05/2018	1.5
200	10/08/2018	1.5
656	10/09/2018	1.5
785	10/02/2018	1
766	10/02/2018	1
305	10/03/2018	1
439	10/03/2018	1
15	10/04/2018	1
301	10/04/2018	1
200	10/07/2018	1
656	10/08/2018	1
766	10/01/2018	0.5
510	10/01/2018	0.5
611	10/01/2018	0.5
200	10/06/2018	0.5

Edited October 16, 2018 by alanf
add text

[comment]

3 minutes ago, alanf said:

I think a selfjoin may be required.

If you only have one table, then yes, of course, a self-join will be required. But why do you need to query the table using a relationship (and not, for example, by finding the records of interest)? What do you intend to do with the results?

 

[alanf]

1 hour ago, comment said:

If you only have one table, then yes, of course, a self-join will be required. But why do you need to query the table using a relationship (and not, for example, by finding the records of interest)? What do you intend to do with the results?

 

Sorry I should have given a bigger picture.  Yes there is more than 1 table.  Lets call it Salespeople.  Based on a relationship of Salespeople::ID, i want to determine the earliest date that a salesperson hit a given goal and grab that date and unit amount only.

Thanks

[comment]

But you're still not saying why you want to do this through a relationship.

The reason I am dwelling on this is that no matter how you do it, there will be three steps to the process:

1. Identify the group of records that meet or exceed the units goal; 
2. Determine the earliest date in this group;
3. Reduce the group to records of that date only.

To do this with relationships, you would have to add two new relationships, in addition to the one you already have: one to include records that meet or exceed the units goal (step #1 above) and enable you to retrieve the earliest date from this group; the other would include records that meet or exceed the units goal and have the earliest date derived earlier. And of course, you would also need to add a calculation field to retrieve said date.

That's a lot of resources to put into something that could be done much more efficiently with a script, resulting in a found set of records. And we still don't know how you plan on using this data, once you get it. For example, if this is for display only, you could use portal filtering to achieve step #3, instead of the second relationship.

 

 

[alanf]

2 hours ago, comment said:

.....And we still don't know how you plan on using this data, once you get it. For example, if this is for display only, you could use portal filtering to achieve step #3, instead of the second relationship.

 

 

Comment.  Thanks for your replies and help...

It will be used for display purposes within an existing portal as an additional field to that table used in the portal.  

If TABLE A is used for the portal and contains one record per ID i want to find ID in TABLE B with UNITS >= to a variable goal number and the earliest DATE record it was achieved.  (ie: TABLE A ID 301 finding a single result in TABLE B of ID 301 with DATE 10/13/2018 using a goal of >=5

I understand how to do a scripted find on TABLE B for ID=301 and UNITS >= goal of 5, but that then returns results for all dates in TABLE B data below and not the earliest date for ID.  Seems I would then have to walk through all the records with repeated finds for each ID in TABLE A and then sort the find by date ASC then grab 1st record and add that DATE to a field in TABLE A for the result???

Guess my thinking i could create a relationship BETWEEN A and B to return the Earliest date for ID with Units >= to a variable goal is not the approach??

 

TABLE A (used in portal) ID 301 302 360 305 TABLE B ID 301	DATE   10/15/2018	UNITS 9
301	10/14/2018	8.5
301	10/13/2018	6.5

360	10/14/2018	4.5
766	10/3/2018	3

 

[comment]

OK, so now it's confusing...

First of all, let's not use abstract names. You have already given your tables sensible names of Sales and Salespeople, so let's stick to these. Also, since the relationship is obviously 'one salesperson to many sales', the table used for any portals will be the Sales table, and the portals will be placed on a layout of Salespeople.

Now, since I am still unsure what exactly you are after, let me outline some options:

(a) Relationship
Suppose you define the following relationship, using a second occurrence of the Sales table:

Salespeople::SalespersonID = Sales 2::SalespersonID
AND
Salespeople::gGoal ≤ Sales 2::Units

and sort the related records on the Sales 2 side of the relationship by Date, ascending. Note: gGoal would be a global Number field defined in the Salespeople table.

Once you have done that, you can place the Date field from Sales 2 on the Salespeople layout, and it will show the earliest date that the salesperson has made a sale that meets or exceeds the goal. There is no good reason to place this field in a portal, because there is only one such date for each salesperson.

Note that instead of sorting the relationship, you could opt for a calculation field (defined in the Salespeople table), getting the minimum value from Sales 2::Date.

(b) An alternative to all this, which I will not go into now, is to use a calculation field using the ExecuteSQL() function.

(c) Script
Starting from a record in the Salespeople table, you could have your script (pseudocode):

• Go to Related Record [from Sales; Show Related Only]
• Enter Find Mode []
• Set Field [Sales::Units; "≥" & Salespeople::gGoal]
• Constrain Found Set []

You now have a found set of all sales records of the original salesperson that meet or exceed the goal. The next steps:

• Sort Records by Date, ascending
• Go to Record [First]

will place you at a record that holds the earliest date that our salesperson has made a sale that meets or exceeds the goal. If you continue with:

• Find Matching Records [Constrain; Sales::Date]

you will have isolated all records with the same date in the current found set. That would be the two records with IDs 766 and 360 in your original example.

---
Let me also point out that both methods look for individual sales that meet or exceed the goal - not (as might be expected) for the earliest date when the total of the sales for that day has met or exceeded the goal.

 

 

 

Edited October 17, 2018 by comment

[alanf]

Comment, thanks for the input and outlined examples. I will avoid abstract names in the future.

( a ) I will try this with a calculation field.  Thanks.

( b ) I had done some thought about using a calculated ExecuteSQL() function today as well.  I am already using this method in other areas of my solution. Thanks

(c ) Here is my weakness and lack of understanding of Go To Related Record.  I need to get comfortable in its ability and usage.

I should have pointed out that the Sales Table example were Running Totals MTD by Day.  Sorry.  

So "not (as might be expected) for the earliest date when the total of the sales for that day has met or exceeded the goal" will work in this case.

Let me play around with these examples and report back.  

Thank you again for the pointers and suggestions!!

[comment]

8 minutes ago, alanf said:

I should have pointed out that the Sales Table example were Running Totals MTD by Day.

Just curious: wouldn't that mean that each salesperson has at most one related record per day in the Sales table? IOW, you would never end up with two records at the end of the script - unlike the original example.

[alanf]

13 hours ago, comment said:

Just curious: wouldn't that mean that each salesperson has at most one related record per day in the Sales table? IOW, you would never end up with two records at the end of the script - unlike the original example.

Yes that is correct.  Sales table would have only 1 record per salesperson per date of the month that they had a recorded sale and that shows cumulative month to date units for that date of the month.  No sure which original example piece you're saying shows otherwise? 

[comment]

1 hour ago, alanf said:

Sales table would have only 1 record per salesperson per date

Good. That might eliminate, or at least simplify step #3 of the process I described earlier.

OTOH, keep in mind that this process returns the earliest date ever to meet the goal; If your numbers reset to 0 every month, and you want the earliest date from the current (or any other specific) month, you have more work to do.

 

1 hour ago, alanf said:

No sure which original example piece you're saying shows otherwise? 

This one:

23 hours ago, alanf said:

Specified Unit Amt of 2 = 2 Expected records returned result

766	10/03/2018	2
360	10/03/2018	2

 

[alanf]

5 hours ago, comment said:

Specified Unit Amt of 2 = 2 Expected records returned result

766	10/03/2018	2
360	10/03/2018	2

 

That shows 1 record for each salesperson who hit the goal  and the earliest date they hit it (in that example 2).

Thanks comment.  

 

The sales table for this purpose was created via a sub summary report on a detail sales table that groups cumulative MTD sales units by Salesperson and Date for a period of start and end dates.  Then that sub summary report  is used to populate the table for this example via export and then imported to the sales table 

Edited October 18, 2018 by alanf

[comment]

OK. Let me just point out that all the suggested methods work for one salesperson at a time. Other approaches are possible - for example, you could produce a summary report from the Sales table. Much depends on how exactly you are going to use the resulting information, how often do you need it, etc.

I also wonder if you really need to reimport the MTD data into a separate table; after all, it contains no additional information that isn't already in your original detail sales table.

Edited October 18, 2018 by comment

[alanf]

True and thanks.  I started this approach of export from a sub summary report from the sales detail table because I could not figure out a way to get the results that the sub summary report produced (Running MTD sales by date of any sales on file for a salesperson) and then use the results in relation to another table by ID of sales person.  So that was my approach to it.