Troubles generating transaction listing for 'Client' - Relationship Issue?

[db_tragic]

I'm having trouble generating a list of transactions (using list view) that are attributed to a given Client.

 

The aim is to have a button on the Client Details layout, that takes me to a List view (Transaction History), where I can then filter the transactions by date, and balance, for all horses owned by the client. (The transactions relate to invoices generated when their horses are treated). I have the date/balance filters sorted.

 

The problem is that when I select the button on the Client Details page(that takes me to the List view, I am getting a list of ALL transactions for all horse owners, no the ones related to the Client I am currently viewing.

 

I suspect there may be a problem with either, or both of, the table I am basing the List view on, and/or the way I have set up the relationships in the graph. Obviously, a subsequent problem with the script I am attaching to the button that takes me there, also.

 

Here's a snapshot of the relationship graph, the script, and the final list view.

 

 

 

Below is the final Transaction list view, but is showing (incorrectly) transactions for all horses, for all owners, however, the Owner name that appears in the merge field at top, in this case "Tania Drever", is correct.

 

 

Should I simply use the value of "Invoice To" to act as a filter, within the script? Or do I need to change an aspect of the relationships graph to have this work properly?

 

Thanks in advance.

 

[eos]

At first glance, relationships and script look OK, and you're targeting the correct TO as per the RG; but is there anything else going on in the script (what we can't see in your screenshot), or maybe a layout trigger?

 

It seems you're landing correctly on one of the records related to your current client (thus the correct merge field display in the header), but maybe then something "Shows all Records”, or otherwise messes up the found set.

 

If you're in doubt about the result of a GTRR, put a portal on your starting layout pointing at the GTRR's target TO. The portal records are the found set after a GTRR with related records only. 

 

btw, this is not pertinent to the issue at hand, but there's something not quite right about the way your Services are related to LineItems …

[db_tragic]

.......... but maybe then something "Shows all Records”, or otherwise messes up the found set.

 

 

 

DOH!

I had put a "Show All" script trigger into the layout - all fixed. Thankyou!

 

 

 

btw, this is not pertinent to the issue at hand, but there's something not quite right about the way your Services are related to LineItems …

 

Glad you spotted this. I did try to get some help on this before, without luck. The current setup doesn't allow me to bring up the individual line items in other layouts (when I try, I only get the first item on the invoice, and the total balance). I have managed around it, but would appreciate your perspective on this, so I know for next time. (Does a different thread need to be started to discuss this?

[Wim Decorte]

keep in mind that "Go To Related Record" is not the only way to produce a found set.  You can also do a straightforward scripted find.

[db_tragic]

keep in mind that "Go To Related Record" is not the only way to produce a found set.  You can also do a straightforward scripted find.

 

My understanding though is that you can't use a calculation in a find - so I found out earlier, hence I've tended to not go down that road (using a scripted find). (I'm still getting my head around some of the nuances of using finds - probably will be doing that for some time to come too!)

 

Nonetheless, thanks for the thought.

[eos]

Glad you spotted this. I did try to get some help on this before, without luck. The current setup doesn't allow me to bring up the individual line items in other layouts (when I try, I only get the first item on the invoice, and the total balance). I have managed around it, but would appreciate your perspective on this, so I know for next time. (Does a different thread need to be started to discuss this?

 

LineItems is a join table for Invoice and Services. It's OK (in fact, it's a good idea) to give line items their own primary key (so each record can be uniquely identified, or could spawn child records of its own), but to reliably relate it to Services, you need a foreign key.

 

The text field service in LineItems isn't strictly necessary; if a line item has a relationship to Services (via the foreign key), you can simply display that field.

 

OTOH, maybe you want to be able to change your Service names/descriptions, while keeping historical invoices accurate as per the names/descriptions used at that time; or want to be able to tweak/modify the name manually on a per-item-basis. If so, make the service field an auto-enter calculation to copy over the name from Services, so it's stored with the line item and won't be impacted by later changes to the name in Services.

 

In brief: in your relationships, use …

 

… Invoices --< LineItems >-- Services

_pk_invoiceID --< _fk_invoiceID / _fk_serviceID >-- _pk_serviceID

 

Neither the _pk_lineItems nor the Service field play any role in these relationships.

[Wim Decorte]

My understanding though is that you can't use a calculation in a find 

 

Enter Find Mode

Set Field [ myField ; <anything can go in here, even a calculation> ]

Perform Find[]  --> no stored find requests in here

[db_tragic]

LineItems is a join table for Invoice and Services. It's OK (in fact, it's a good idea) to give line items their own primary key (so each record can be uniquely identified, or could spawn child records of its own), but to reliably relate it to Services, you need a foreign key.

 

The text field service in LineItems isn't strictly necessary; if a line item has a relationship to Services (via the foreign key), you can simply display that field.

 

OTOH, maybe you want to be able to change your Service names/descriptions, while keeping historical invoices accurate as per the names/descriptions used at that time; or want to be able to tweak/modify the name manually on a per-item-basis. If so, make the service field an auto-enter calculation to copy over the name from Services, so it's stored with the line item and won't be impacted by later changes to the name in Services.

 

In brief: in your relationships, use …

 

… Invoices --< LineItems >-- Services

_pk_invoiceID --< _fk_invoiceID / _fk_serviceID >-- _pk_serviceID

 

Neither the _pk_lineItems nor the Service field play any role in these relationships.

 

Thanks for clarifying that. I can clearly see your point as I look at it now. When I started on this database project 18months ago, I understood far less than I do now (in fact it was my first real go at a FileMaker solution), hence the slip up. Knowing what I know now, I would have done just what you had suggested. Perhaps I should review a few of the things I did earlier in case there's other issues there I haven't seen yet!

Enter Find Mode

Set Field [ myField ; <anything can go in here, even a calculation> ]

Perform Find[]  --> no stored find requests in here

 

Hmm - I got this advice earlier from 'comment':

 

http://fmforums.com/forum/topic/90981-filter-field-in-list-view-using-drop-down-menu-items/?p=417857

 

Perhaps is in a different context?

[comment]

My understanding though is that you can't use a calculation in a find

 

To clarify:

 

1. You cannot use a calculation formula in a stored find request. For example, to find records with current date in Datefield, you can:

Enter Find Mode[]
Set Field [ Table::Datefield; Get ( CurrentDate )  ]
Perform Find[]

 

But you cannot:

Perform Find [ Specified Find Requests: Find Records; Criteria: Table::Datefield: “Get ( CurrentDate )” ]
[ Restore ]

--

 

 

2. You cannot perform a find, using any method, if the search itself needs to perform a calculation in order to find/omit a record. For example, you cannot perform a find for records where Field A = Field B.

 

[db_tragic]

To clarify:

 

1. You cannot use a calculation formula in a stored find request. For example, to find records with current date in Datefield, you can:

Enter Find Mode[]
Set Field [ Table::Datefield; Get ( CurrentDate )  ]
Perform Find[]

But you cannot:

Perform Find [ Specified Find Requests: Find Records; Criteria: Table::Datefield: “Get ( CurrentDate )” ]
[ Restore ]

--

 

 

2. You cannot perform a find, using any method, if the search itself needs to perform a calculation in order to find/omit a record. For example, you cannot perform a find for records where Field A = Field B.

 

 

Hi comment,

 

Thanks for making that distinction. That's a good clarification.

 

Cheers,