Yet another portal filtering question

[HiredGun79]

Hello,

I have a customer table that has a few fields that are boolean - blacklisted, closed, potential.

I have a filtered portal where the user can choose a customer. The portal is filtered based on a low match and a high match (like in the book (Using Filemaker 7, Special Edition). The user types in the first few letters of the customer's name and the portal filters down.

The effect I want is to be able to have the user be able to click buttons for blacklisted, closed and potential.

If no buttons are clicked, then the portal is initially filtered to not include customers who are blacklisted, closed or potential.

If the closed button is clicked, then the portal displays all customers except the blacklisted and potential customers.

Blacklisted and closed, then portal shows all customers but potential.

The name filter will still work no matter what buttons are clicked. .

Basically, if a button is clicked, it allows those records to be displayed. .

Does this make sense?

Any help would be greatly appreciated! Thanks!

[RalphL]

I think that you will need to use not equal relationships between your buttons and blacklisted, closed & potential. You could use a set of calculated fields not equal to the button so that when the button is not clicked the calculated field will be true. Then the not equal relationship will fail and the records with that setting will not show.

[HiredGun79]

Ok, I know I'm missing something. . Here's the file I'm working with (some parts are broken - I took out a large chunk of the file to slim down things. . )

I have a few sample customers in the file. . What I'd like is for the "Choose Customer" layout (the layout that has the filtered portal) to only display the customers who are not blacklisted or closed or potential.

Then if the user clicks the button, say the closed button, then the portal will display customers who are not blacklisted or potential. .

etc. for blacklisted and potential, so if all three buttons are clicked, then the filtered portal shows all customers.

Thanks in advance for the help!

filtered_portal_help.fp7.zip

[-Queue-]

Use >= criteria for each 'match' field to 'customer' field in your relationship. So it looks like

match_low <= customer_name

match_high >= customer_name

match_blacklisted >= customer_blacklisted

match_potential >= potential_customer

match_closed >= customer_closed

[RalphL]

What are the password and user?

[-Queue-]

I just selected Guest account.

[HiredGun79]

Ok, this is the weirdest thing I've seen working with Filemaker. . .

The real file I'm working on has thousands of confidential records for my client - so I ripped out all irrelevant portions of the database I posted in this forum. .

I mention this because that must have had some effect on the results in that filtered portal (somehow, how, I have no idea). .

When I create the >= relationships in the test file I posted in the forums, the portal works correctly! Problem solved! Perfect.

When I do the identical thing in the real file, no results turn up in the filtered portal. . not when no buttons are checked, nor when any combination of the buttons are checked.

This does not make sense to be. . I understand the principle behind relating the blacklisted, closed and potential flags. . I know why it works in the test file. . but it does not work in the real file. . .

I'm going to make a copy of the real file and slowly trash parts of the database and check each time I modify something if it allows the relationship to work. .

The weird thing is that the only data involved in that portal is the customer table, the customer filter table and the graphics table (barely the graphics, it pulls global containers). .

Obviously, I didn't touch anything in those tables. . . The relationship graphs are identical between the two files. .

Does anyone have any idea what's going on?

Thanks

[HiredGun79]

Ok, ok :

I know what's going on. . why it's not working. . I don't know. .

In the original file, the potential_customer field in the customer table is a calculation. . . here it is. .

If ( Count ( order::order_id ) > 0 ; 0 ; 1 )

I'm relying on the count function when applied to related tables. . if there are 1 or more order records that are related to the customer table (by a foreign key relation: customer::customer_id = order::customer_id ) then the value of potential_customer is 0

The customer is not a potential customer, the business has used the customer before thus it's a real customer, not a potential customer. .

If there are no related records, then the customer must be potential since the business has not used them on an order.

Does this make sense?

I theoretically could keep the potential_customer field a number and set it via a script that checks to see if a customer had been added or removed from an order. . but that would get mighty tricky since customers can be on more than one order. .

I'm positive the calc function is working - the potential_customer field in every customer record does have a value of either 0 or 1. . .

Anyone know why a calc does not work in this instance?

Thanks again. .

Edited August 16, 2005 by Guest

[RalphL]

If I change the relationship to:

match_low <= customer_name

match_high >= customer_name

match_blacklisted <> customer_blacklisted

match_potential <> potential_customer

match_closed < customer_closed

I get your result. There may be other combinations that will produce this. I hit on this one by accident.

[HiredGun79]

I've tried your solution and I could not get it to work. . are you meaning the <> symbol to be cross product or not equal? I tried both and still could not get same results. . .

Thanks for the response!

[RalphL]

<> is not equal, but your problem lies in the fact that you are using a calculation that uses a related field making it unstored. That makes the relationship invalid. If you look at the relationship graph you should see a vertical line next to Potential Customer in the Customer TO.

[HiredGun79]

Ahh, ok I understand about using the unstored calc in a relationship. . that's a little counter-intuitive because when you look at the values of the calc, it always has a value and you'd think you could relate that value via the relationship graph. . .

You see what I'm trying to do - with the potential customers. .

Are there any workarounds to use a calculated value in a relation?

[RalphL]

Try making it an auto-entered calculation, set it to replace existing value. Calculations are OK it is the unstored part that is killing the relationship.

[HiredGun79]

Ok, let me give that a shot. . . brb with results.

[HiredGun79]

Well. . that would work, if the only time the potential customer flag changed is when the customer record is created. . . However. . the nature of that calculation needs to be unstored to remain accurate. . .

That is unless I am not using auto-enter correctly. . . The way I have it set now, it only evaluates the calculation and inserts correct potential customer information is when a customer is created. .

At that point, it's clear that the customer would not be on an order since it was just created. . so it makes all new customers potential. . once the customer is added to an order and ceases to be potential, the potential_customer field does not reflect the change. . .

Is there a way to force the auto-enter to update? Is there another work around?

Thanks again for your patience and help. I really appreciate it.

[RalphL]

Have you allowed it to replace an existing value?

[-Queue-]

Ralph, I'm curious how you got

match_low <= customer_name

match_high >= customer_name

match_blacklisted <> customer_blacklisted

match_potential <> potential_customer

match_closed <> customer_closed

to work. If you select match_blacklisted, then only those records which are not blacklisted appear, which is the opposite of the desired effect. Also, the relationship has to be a sort of OR instead of an AND, so that multiple combinations of the booleans will relate instead of only exact ones. This is why I used the >= operator. If one of the 'match' fields is selected, then related records will contain either a zero or 1 in the correlating field. If two 'match' fields are selected, then related records will contain a zero or 1 in both correlating fields, etc. If no 'match' fields are selected, then related records must contain zero in all fields.

[RalphL]

I wasn't trying to get it to work right, I was trying to get the same failure that hiredgun was geting.

[HiredGun79]

Yes, I have it replacing existing value. . . at what point does an auto enter calculation "refresh" ?

For the 700+ customers already in the database the potential_customer field has no value - when I create a new customer, the potential_customer has a value of 1, yes, which makes sense because a newly created customer wouldn't be related to any orders. . .

[RalphL]

It should refresh when the result of the calculation changes.

For older calculations you may have to replcae by calculation to set you initial value.

[-Queue-]

To clarify, it will refresh when any local value of the calculation changes. It will not update when a related value is changed, unless you set the key field to itself to force a refresh.

So,

If( Count(order::order_id) > 0; 0; 1 )

which, by the way, is equal to

IsEmpty(order::order_id)

will only update for each record when the local key field for the order relationship is changed or set to itself.

[HiredGun79]

I apologize for being so obtuse. .

How would I set the key field to itself to force a refresh?

I'd like the potential_customer field to refresh every time a related order record is changed. . either by the count or the IsEmpty. .

Would that use the evaluate calculation?

[RalphL]

Set Field (KeyField; Keyfield)

The Refresh Window with the Flush Cached Join Results may work. The Commit step may also work.

[HiredGun79]

Ok, I created a new customer with the potential_customer field being simply the If - Count statement. .

Initially, it worked terrific - The potential customer set to "Yes" - just as it is supposed to. . .

Then I add that customer to an order. . Unfortunately, the potential_customer field does not change to "No" like it did when it was an unstored calc.

Here are the field settings for potential_customer. . .

"Indexed, Auto-Enter Calculation replaces existing value, Evaluate Always, By Value List, Allow Override"

So I know I have it replacing existing value, like you said earlier. . . still not getting me the result I require.

Thanks for the many posts, you're helping me out a lot!

[HiredGun79]

Ok, let me add that to the choose customer script. . . that should do the trick.

brb.

[HiredGun79]

Ok, I cannot get this thing to work. . Here's the original file I posted, but with an order table added with two fields, order_id and customer_id. . .

I also have added a script called "Update Potential Customer" in the script menu. .

There I have placed the set field (keyfield, keyfield). . so it's easy to run that script to test to see if it's working.. .

I must be doing something incorrect.

Thanks again for all the help!

filtered_portal_help_v2.fp7.zip

Edited August 18, 2005 by Guest
Forgot to add file!

[-Queue-]

The relationship's local key field is customer::customer_id not customer::potential_customer. Setting customer::potential_customer to itself will not change anything. Setting customer::customer_id to itself will refresh customer::potential_customer's value.

[HiredGun79]

Ahh, THE local key. . . Man, I was dense. . thought you all meant like "the key field of the calculation" as in the important field. . not THE key field. .

Let me give this a shot. . . Thanks

[HiredGun79]

This'll work, I just have to go back and add a script step to update the local key whenever it's possible that a customer will be added or removed from an order. . . I bit more tedious than a simple calc field, but it's a solution nonetheless.

Thanks again for the discussion and I'll see you all on the boards.

[onefish]

I tend to use eventscript to triger a script that updates the related record whenever a value in the trigger field is updated. For me, it has been the most consistant way to get the result you are looking for. The related field can then be indexed and used in relationships.

Edited August 28, 2005 by Guest