Transactions Reporting Quandary

[BerkeleyBernie]

I'm going to use a business model to explain the reporting I'm trying to do.

 

We have Salespeople, their Prospects, and the Transactions that the Prospects make.

 

There are multiple Prospects assigned to each Salesperson, but each Prospect only has one Salesperson assigned to them.

 

Each Prospect has zero or more transactions.

 

I can easily create two reports to cover the ground I want, but I'm puzzled as to how to combine both reports into one.

 

1) I can create one report showing the Salespeople and all their Prospects, and also include summary data subtotalling and totalling all the transactions their Prospects make. This report is in the Prospects table.

 

2) I can create another report grouping each transaction under their Prospect, and grouping each Prospect under their Salesperson. I can, of course, also summarize this data, restrict transactions by date, amount, etc. This report is in the Transactions database.

 

The second report does everything I need except ONE thing: if a Prospect has not made any transactions, they will not show up in the report. I'd like the report to show ALL Prospects, even if they have not yet made a transaction, and I'm wondering if there is a way to do this.

 

I could throw Portals into the first report, but that is an ugly hack, and also doesn't have the flexibility in constraining the transaction data that the second report does. Or I could throw dummy records into the transactions database for Prospects without transactions, but that poses other problems (i.e. if some constraint excludes the dummy record).

 

I want ALL Prospects to appear, grouped by their Salesperson, regardless of whether they've yet made any transactions. Is what I want to do possible in FMP 5/6? Or clever workarounds?

 

Thank you.

[AHunter3]

The second report does everything I need except ONE thing: if a Prospect has not made any transactions, they will not show up in the report. I'd like the report to show ALL Prospects, even if they have not yet made a transaction, and I'm wondering if there is a way to do this.

 

Yep, that's the limitation of the built-in subsummary report.

 

Try this:

 

Assuming that for Transactions you would want to display more than one field (e.g., date, amount, widget SKU number, comment), create a text calc field in Transactions that concatenates those values, e.g.,

 

DateToText(Transaction Date)&" "&Amount&" "&widget SKU number&" "&Left(comment, 20)

 

call that TransactionString.

 

Now make a value list in Prospects, showing related values of TransactionString. Call it "Transactions"

 

Now create a calc field, unstored, ValueListItems(Status(CurrentFileName), "Transactions"). Call it TransactionsVL.

 

Run your report in Prospects; in the body part, below the Prospects fields, put a merge code, = just this text entered on the layout in layout Mode: >.

 

Click on that text object to make the four drag-handles appear, and yank down and to the right to accomodate the largest likely necessary space. Don't worry about overshooting the maximum layout space to fit your page setup page definition, make it deep. Then while it's still clicked to to Sliding/Printing and set it to slide up and reduce the enclosing part, and do likewise for any object on this layout that's below that text object.

[BerkeleyBernie]

Thanks so much for your generous reply. Very clever! Those are 2 features of FMP 5/6 I hadn't yet learned or found a reason to learn (value lists based on related values, ValueListItems in defining a calc field or script step). Seems like the related value list feature could be especially useful. Any tips on how you use it, suggestions on other situations where it might be useful?

 

The report works as you described, but would be fixed to values defined by the relationship, correct? So if the relationship shows all related values in Transactions for a Prospect, then there is no way to restrict the date range or other values (i.e. amount >25) on the fly. One would have to create other specific relationships to describing those constraints, and I am trying to design it so the enduser has easy flexibility in building the report to the constraints they want to see (but always including their entire list of Prospects).

 

I think the flexible solution is the dummy record one, a special non-$ transaction that describes the date a Prospect is assigned to a Salesperson. All I need to do to make sure those records are included in all queries is, in the report script, add a Perform Find script step after all user-defined queries to find those records and add them to the existing found set.

 

Thank you again for your clever and clear advice.

[AHunter3]

Well, the most common use of related value lists is to have two fields in Table B (let's say Category and SubCategory) and a main table, Table A, that also has Category and SubCategory fields; the tables are related by Category = Category. From the perspective of Table A you create a value list of Table B::SubCategory based on related values only.

 

In Table B your records might look like this:

 

Cuisines..........Main Courses

--------------------------------

Italian..........Chicken Parmesan

Mexican..........Chiles Rellenos

French..........L'Entrecote

Italian..........Scaloppine Alla Paesana

Mexican..........Carne Adovada

French..........Canard Aux Champignon

 

In Table A, you pick the main Category (cuisine) first, and the value list that drops down from the SubCategory (main course) field, you see only the related values: if you pick "Mexican" you see "Chiles Rellenos" and "Carne Adovada".

 

So if the relationship shows all related values in Transactions for a Prospect, then there is no way to restrict the date range or other values

 

Well, not "on-the-fly", maybe, but if you anticipate doing it by date, you could create a new relationship based on the combo of whatever fields you're using to join Prospects and Transactions now plus (let's say) a global date field in Prospects and the Transaction Date in Transactions:

 

Prospect ID & "|" & g.SelectedDate

 

Prospect ID & "|" & Transaction Date

 

Then you'd need a new value list of related values based on that relationship and a calc field of ValueListItems, but now you can show only the dates you want.

 

 

You can also use a "multikey" strategy in which the same relationship can be deployed for more than one function, e.g.

 

Prospect ID & Case( not IsEmpty(g.SelectedDate), "|" & g.SelectedDate)

 

Prospect ID & "¶" &

Prospect ID & "|" & Transaction Date }

 

the hard return coded into the latter means that one calc field reconciles to two values (one above the other) and in a relationship either one will match up with a value on the other end, hence "multikey".

[BerkeleyBernie]

Thanks again for the excellent explanations. I can see how the related value list could be useful, also in a "drill" down fashion (i.e. a taxonomy table).

 

I understand the multikey example, and can think of other ways that could be useful. In this case, though it allows some user input, it would only be useful in cases where you want the relationship based on a fixed value (a specific date, amount, etc.) It wouldn't work in any case that finds a range (i.e. ">1.1.2005" or "25...50"). I haven't thought it through, but I'm thinking it might also be a pain to include, in addition to the details, any constrained summary values based on a constrained set formed by such a relationship.

 

I greatly appreciate the advice. I'm familiar with Scriptology, but if you have any other resources you'd recommend for such tricks, I'd be interested to hear of them.

[AHunter3]

It wouldn't work in any case that finds a range (i.e. ">1.1.2005" or "25...50")

 

Do a Google search for "smartranges" and "filemaker"

[BerkeleyBernie]

Do a Google search for "smartranges" and "filemaker"

 

Hehe. So I take it you are referring to the plugin, not just the technique? Of the two they offer, is there a big advantage in DataFilter over SmartRanges?

[BerkeleyBernie]

Any other plugins you consider essential as a developer (not just for unusual cases)?

[AHunter3]

Nope

 

Back in the FileMaker 4-5 days, one of my recurrent rants on the FSA list was that too many folks went straight for a plugin when you could do what they were trying to get done perfectly well using FileMaker-native functions.

 

I once opined that there was probably a market for a plug-in that would let you do this:

 

Go to Layout [specify layout]

Perform Find [restore]

Sort [restore sort order]

Page Setup [restore]

Print

 

 

The purpose of plugins is to extend native capabilities to do specialized things.

 

In some eras of FileMaker history there have been, umm, "holes" or "bugs"... FileMaker 6 came out with Search and Replace with no ability to disable it in the Access Privileges screen, so the plugin SecureFM got real popular among folks who had never deployed a plugin before. But I don't see any obvious "holes" of that nature in today's FileMaker.

[BerkeleyBernie]

So...you keep a copy of the Filemaker smartrange algorithm around and paste it in whenever you need it (with appropriate fieldnames inserted)? Is there a copy of the complete algorithm avaialble online? I did read through part of Mikhail Edoshin's article.

[Maarten Witberg]

my 2c... if you're going to do a lot of work with ranged filters and in fact any filtering with more than one field on each side, upgrade to version 7/8. you can do this at upgrade price till december 20 if I'm not mistaken. No, this is not a commercial break.

 

maarten

[BerkeleyBernie]

Found his example file, thanks. For anyone reading this thread, article here:

 

DELETED

 

Example file here:

 

DELETED

[Mian Umar]

Well, the most common use of related value lists is to have two fields in Table B (let's say Category and SubCategory) and a main table, Table A, that also has Category and SubCategory fields; the tables are related by Category = Category. From the perspective of Table A you create a value list of Table B::SubCategory based on related values only.

 

In Table B your records might look like this:

 

Cuisines..........Main Courses

--------------------------------

Italian..........Chicken Parmesan

Mexican..........Chiles Rellenos

French..........L'Entrecote

Italian..........Scaloppine Alla Paesana

Mexican..........Carne Adovada

French..........Canard Aux Champignon

 

In Table A, you pick the main Category (cuisine) first, and the value list that drops down from the SubCategory (main course) field, you see only the related values: if you pick "Mexican" you see "Chiles Rellenos" and "Carne Adovada".

 

 

 

Well, not "on-the-fly", maybe, but if you anticipate doing it by date, you could create a new relationship based on the combo of whatever fields you're using to join Prospects and Transactions now plus (let's say) a global date field in Prospects and the Transaction Date in Transactions:

 

Prospect ID & "|" & g.SelectedDate

 

Prospect ID & "|" & Transaction Date

 

Then you'd need a new value list of related values based on that relationship and a calc field of ValueListItems, but now you can show only the dates you want.

 

 

You can also use a "multikey" strategy in which the same relationship can be deployed for more than one function, e.g.

 

Prospect ID & Case( not IsEmpty(g.SelectedDate), "|" & g.SelectedDate)

 

Prospect ID & "¶" &

Prospect ID & "|" & Transaction Date }

 

the hard return coded into the latter means that one calc field reconciles to two values (one above the other) and in a relationship either one will match up with a value on the other end, hence "multikey".

 

Hello AHunter3

[Mian Umar]

Well, the most common use of related value lists is to have two fields in Table B (let's say Category and SubCategory) and a main table, Table A, that also has Category and SubCategory fields; the tables are related by Category = Category. From the perspective of Table A you create a value list of Table B::SubCategory based on related values only.

 

In Table B your records might look like this:

 

Cuisines..........Main Courses

--------------------------------

Italian..........Chicken Parmesan

Mexican..........Chiles Rellenos

French..........L'Entrecote

Italian..........Scaloppine Alla Paesana

Mexican..........Carne Adovada

French..........Canard Aux Champignon

 

In Table A, you pick the main Category (cuisine) first, and the value list that drops down from the SubCategory (main course) field, you see only the related values: if you pick "Mexican" you see "Chiles Rellenos" and "Carne Adovada".

 

 

 

Well, not "on-the-fly", maybe, but if you anticipate doing it by date, you could create a new relationship based on the combo of whatever fields you're using to join Prospects and Transactions now plus (let's say) a global date field in Prospects and the Transaction Date in Transactions:

 

Prospect ID & "|" & g.SelectedDate

 

Prospect ID & "|" & Transaction Date

 

Then you'd need a new value list of related values based on that relationship and a calc field of ValueListItems, but now you can show only the dates you want.

 

 

You can also use a "multikey" strategy in which the same relationship can be deployed for more than one function, e.g.

 

Prospect ID & Case( not IsEmpty(g.SelectedDate), "|" & g.SelectedDate)

 

Prospect ID & "¶" &

Prospect ID & "|" & Transaction Date }

 

the hard return coded into the latter means that one calc field reconciles to two values (one above the other) and in a relationship either one will match up with a value on the other end, hence "multikey".

 

Hello AHunter3

I try the upper portion of this thread

I use these terms but i am unable to do conditional value list ( Mean its not solve)

Plz Help me in it

can u make a file like this and send me

i am very thankful to u

Mian Umar

[AHunter3]

Sure, why not?

[AHunter3]

Mian Umar!!!

 

QUIT asking the same question in a second thread when you don't like the answer you got in the first thread.

 

I wasted time making an example file for you, not remembering (or not knowing) that you had already been referred to an example file someone had already posted, and not knowing you had already been told you had to join up in order to download example files.