Adding color to the relationship

[rborge]

I have a portal that displays data from an invoice layout. I am able to sort the portal by either date range, status (ie paid, not paid etc) and by employee. Here are the fields relating the status variables:

(Portal) (Invoice)

cbilling_status=status

cbilling_status (calc) Case(IsEmpty(x_status); ValueListItems(Get(FileName); "Billing Status"); x_status)

xstatus (text): global field, located in the portal header, allows me to see all invoices if it is left blank

status (text, indexed): Pulls data from a drop-down list named "Billing Status"

I would like the values for "Billed", "Paid" etc to have individual text colors (blue for "Paid", red for "Not Paid" etc). I changed the status field to the following calculated result:

Case(status="Billed";TextColor ( status ; RGB ( 0 ; 38 ; 177 ));status="Not Billed";TextColor ( status ; RGB ( 150 ; 0 ; 165 ));status="Disputed";TextColor ( status ; RGB ( 255 ; 0 ; 0 ));status="Paid";TextColor ( status ; RGB ( 0 ; 140 ; 75 )))

Unfortunately, although the proper colors appear in the portal, it no longer sorts properly. How can I keep my relationshps intact and still display the text in the proper colors? Thanks.

Rick

[John Mark Osborne]

My guess is you have a calculation resulting in a number and not text. Enter Define Database and double-click on the cbilling_status field. Look at the bottom of the calculation and see if the calculation result is number. If so, change it to text.

[rborge]

c_billingstatus generates a text result. I suspect that the problem is with the calculation used in c_billingstatus:

Case(IsEmpty(x_status); ValueListItems(Get(FileName); "Billing Status"); x_status

Specifically, the calculation looks for the "valuelistitem" named "Billing Status" (a pull down menu). When I change the field "status" to a calculated result (text by the way), the valuelist "Billing Status" is no longer being used and the relationship no longer is valid. Any thoughts on how I can modify the above calculation to incorporate the calculated result in the status field? Thanks.

Rick

[comment]

Can you post a demo showing the problem? Based on your description, it should work.

One thing that is not clear to me, is how are you "able to sort the portal by either date range, status (ie paid, not paid etc) and by employee". Do you go into Layout mode each time you want to change the portal's sort order?

[rborge]

The portal is a table occurance of a client table which has 4 relationships to an invoicing table. The relationships are:

(billing portal) (invoice)

c_billingstatus=status

c_physician_billing=physician_billing

c_datelowbillingportal<=procedure_date

c_datehighbillingportal>=procedure_date

All of the calculation fields ("c's") above, are used to allow all records to be displayed if no more specific sort criteria (eg "billled", "Dr X") are chosen. The portal sorts dynamically from Browse mode.

I'm not sure how to post a demo as you suggested. By the way, is there a way to write a script that will simply change the color of text in a field based on what word is in that field? That way, my relationships would remain unchanged and I could simply change the color after the sort has taken place. Thanks again.

[comment]

Let's not have two issues going at once. Sort has nothing to do with your relationship working or not. IIUC, you started with the relationship working, but not sorting properly. If it sorted OK with plain text, it should continue to sort the same way with styled (colored) text.

But you haven't answered my question. What do you mean by:

"The portal sorts dynamically from Browse mode."

Portals do not sort dynamically. Portals sort by the sort order specified in the portal's definition, or default to the sort order of the relationship.

To post a file, make it, zip it, and attach it (click Reply, and you'll see a 'Manage files' link).

[rborge]

I have attached a copy of my database. Choose the "Billing List" button to view the portal. The contents of the portal update when new filter parameters are chosen. This occurs in Browse mode. Currently the portal relationships all appear to work. All data is essentially in black and white. I'm sure that I'm not using the proper terminology to adequately describe things. Take a look and see what you think. Thanks.

Rick

Procedure_Data_Base_copy.fp7.zip

[comment]

It appears to be working fine. Where is the problem?

[rborge]

I want to make the "Billed" and "Paid" items in the portal appear in colors (eg red for "disputed", green for "paid").

[comment]

I am afraid I don't know how to help you. You started by saying that you have already done this, and that the portal stopped sorting properly as a result.

FWIW, your portal is sorting by Date alone, and I believe it will continue to do so regardless of what you do to the Status field (unless you manage to break the relationship in the process - but then the problem will no longer be with sorting).

Please, either demonstrate the sorting problem, or restate your true problem. Otherwise this is not going anywhere.

[Lee Smith]

I want to make the "Billed" and "Paid" items in the portal appear in colors (eg red for "disputed", green for "paid").

In the [color:red]Invoice TO, add this calculation to the Field [color:red]Status, as an option.

Case ( status = "disputed" ; TextColor ( status ; RGB ( 100 ; 0 ; 0 ));

status = "paid" ; TextColor ( status ; RGB ( 0 ; 100 ; 0 ));

status = "billed" or status = "not billed"; TextColor ( status ; RGB ( 100 ; 100 ; 100 )))

Be sure to deselect the button [color:blue]Do not replace existing value in field (if any)

It is located just below the Specify Button, but can't be deslected until after pasting in the calculation.

HTH

Lee

[LaRetta]

Good calc, Lee, but that is essentially the same calc as the original post. Status *is* in Invoices and is the field which was changed (to a calculation) - the fields even turned color. However, it makes sense to me (in my own twisted way) and this MAY be what is happening ...

"When I change the field "status" to a calculated result (text by the way), the valuelist "Billing Status" is no longer being used and the relationship no longer is valid."

Chain of Disintegration (as I see it)

1) cBillingStatus is based upon Value List which is based upon Status.

2) Status is changed to indexed calculation. Rick said it produced colored text in the portal (at one time) so the relationship was working (which means Status was indexed) and the calc was working as well.

3) At some point, Status is changed to [color:red]unstored calculation.

4) Value list evaporates.

5) Relationship breaks because there is nothing in the value list in which to populate the key field cBillingStatus and Status isn't indexed.

These are the issues Michael was attempting to pin down and only Rick can answer them but portal sort would not work if relationship is broken. But that sort button is broken as well - pointing to a non-existant script(?). Was that the attempt at dynamic sort? I didn't mean to step in either ... but I adore mysteries.

Get your butt back here, Rick, so we can find out 'The Rest of the Story.'

LaRetta

[Lee Smith]

Oh darn it, I didn't realize that the file had changed.

The calculation worked fine on the file I downloaded. Hope I didn't mess things up more thant they were than.

Lee

[LaRetta]

You didn't mess up a thing. And I don't think the file has changed. There IS no calc in Invoices on the Status field. But yes, your calc (or Rick's original one) - as Michael pointed out - work fine. The issue isn't in the calc (or in the stars, dear Brutus) but in it's unstoredness (me thinks).

Michael said it ... "...unless you manage to break the relationship in the process - but then the problem will no longer be with sorting."

Yep.

[comment]

But, in your scenario, the result would have been no records in the portal. Not:

Unfortunately, although the proper colors appear in the portal, it no longer sorts properly.

[rborge]

Sorry for all of the confusion. My original post described how I had modified the file that is linked above and a description of how those modifications resulted in a non-functioning portal. I restored the database back to its working form and that is what is linked above. I am going to retry the above noted calculation and will relink the "new and improved" database here later today. Thanks for everyones input, I'll try to clarify things after work.

Rick

[rborge]

The following changes were made:

1) The status field is still a dropdown using choices from the "Billing status" dropdown list (this is unchanged)

2) In define database, I the status field is still a text field, now with a calculated (text) result using the calculation suggested above. The "Do not replace existing value in field (if any)" was deselected and may have one of my original problems.

3) I redefined the script for "sort records" to do just that (sort records).

The colors now work and the portal will sort appropriately but only if the sort records button is used. This is opposed to the other fields such as date range and physician that result in an updated portal immediately after selecting the desired filter (such as range of dates, individual physician etc).

Is there a way to attach the "sort records" script to the color calculation so that the portal refreshes immediately? I have attached a copy of the database for your review. Thanks again.

proceduredatabasebackup.zip

[LaRetta]

As Michael pointed out, the Status calculation has no effect on sorting. That sort button sorts your Patients. It doesn't (never has and never will) sort the portal. Your portal sorts in descending order on Procedure Date as specified in the portal sort option. In addition, your sort script (which won't sort your portal anyway), has no sort defined. But all it could ever do is sort your Patients anyway.