Finding in a Portal

[Mark Ballard]

Hi, Please excuse my lack of ability!

I have a "bookings" portal on the main "patients" page and i wish to do a find on this portal to look for any bookings that have not been invoiced, but only those relevant to that patient. I can make this happen in a separate layout no problem (try the show uninvoiced bookings on bookings layout script) but can't work out how to do this within the "bookings" portal on the "patients" layout.

I've tried a few different ways but can't ssem to make it happen. Any pointers or a proper solution would be very greatfully received. Thank you

Patient_6.fp7.zip

[Lee Smith]

If I'm understanding you correctly, there is a button beneath the Portal that says it will do what you are asking. If it isn't doing what you expect it to, maybe you should contact the original developer.

Lee

[Mark Ballard]

Hi Lee, Thanx for the speedy response, I'll take it as a compliment, I am the original developer!

All i want to do is get the bookings portal to indicate the bookings that have not been invoiced yet, ie without an invoice number, when clicking on the affore mentioned button. I've tried several ways but it just doesn't seem to happen.

[LaRetta]

Your Payments to Invoices Table Occurrence Group: You have Payments::PaymentID = Invoices To Payment::PaymentID

But payments is a many to one invoice. Change that connection to: Payments::InvoiceID = Invoices To Payment::InvoiceID

Then, in Invoices, create a calculation (number) called cOutstanding. Evaluate from context of Invoices To Payments (very top popup) and select fields from Invoices To Payments TO. Calculation would be: InvoiceTotal - Sum(Payments::Amount)

Script to find outstanding:

Enter Find Mode [ ]

Set Field [ Invoice To Booking::InvoiceTotal ; ">" & 0 ]

Perform Find [ ]

That should do it.

UPDATE: Oh, also you have a Patient with ID 0 in invoices but you have no Patient with that ID. And I should add, as your file size grows, searching for an unstored calculation (cOutstanding) with become a pain. I just wanted to give you a heads-up on it. You can handle that in various ways but it's a big subject (with many Forum posts about it). So I suggest you study those posts and decide which options you want to use to address the issue. Searching unstored (if record-size is kept small, ie, under 100,000 invoices) isn't too bad...

LaRetta

Edited March 21, 2006 by Guest
Added update

[Mark Ballard]

Hi LaRetta and thanx for your post.

I have tried all of your suggestion but sadly still no joy. I think i have probably not explained what i'm trying to do correctly as well.

On the "Bookings" portal there is an invoice checkbox. The idea is that when you want to invoice for a booking, check the box and then hit the Invoice or Insurance invoice button. That all works well. What i am struggling to create is a script to find any bookings that have not yet been attached to an invoice but only for the patient record that you are currently in. Basically a search for a related booking without an invoiceID attached to it, results shown in that portal. It seems like such a simple thing to do but i just cant suss it!

I think your soultion deals with the invoice totals and payments, which to be honest i have yet to get to!

As ever, very greatful if you have any other help.

[LaRetta]

Well, I solved a problem you would have hit in the future then.

You want to search your Bookings TO for empty invoice number field.

Script:

Perform Find [ Restore ]

Specify Find Request would be on Bookings::InvoiceID. Just type an = in the criteria.

But if you want the portal to ONLY contain unassigned invoices, you will need to filter/adjust the portal. I don't see that as necessary. You will end up with a set of Patients with unassigned bookings regardless. If you want to have your portal adjust, let me know. But you should preserve your original Bookings TO and use a new filtered TO in its place ... once which can handle your request.

L

[Mark Ballard]

Hi LaRetta, sorry for the delayed response, the time difference meant bed was calling.

I have made a couple of mods to my file just to show what i really want to see, even though i can't do it!

The "Bookings" portal (top one) shows the bookings and any invoice numbers that have been assigned to them via the Invoice button and script.

I need to see two options on that portal both showing only related records to the patient record that you are currently on.

Option 1, default, shows all bookings related to that patient. Basically as it is, works fine.

Option 2, shows only related bookings that do not have an invoice number attached. I need this to work via a script that i can trigger off the "Show Uninvoiced" button.

Just to help, I've added a temporary button below this "Show Uninvoiced in a separate window" which does exactly what i want but in a separate window. I need to get this result, but in the portal.

Feel free to play with the file and if you can get it going it would stop my head from hurting from all the banging against the wall }:(

Patient_6a.fp7.zip

[LaRetta]

Hi Mark,

Create another table occurrence of Bookings, maybe called Bookings Invoices. Re-specify your Bookings portal to this TO instead. Remember to change all the fields within the portal as well.

Process:

Create a global (text) in PATIENTS called gInvoiced.

Join Bookings Invoices::PatientID = Patients::PatientID

AND

Bookings Invoices::InvoiceID < Patients::gInvoiced

Attach script (can be simple Specify Button) to your Show Uninvoiced button with:

Set Field [ Patients::gInvoiced ; Abs(Patients::gInvoiced) < 1 ]

LaRetta :wink2:

Edited March 22, 2006 by Guest
Corrected Bookings TO name

[LaRetta]

I should clarify that this new portal will, when clicked (it is a toggle), show ALL Bookings for that Patient and, when clicked again, show only unInvoiced bookings. 1 is show all, 0 is show only those yet uninvoiced.

You can script it however you wish. Since I used a global, it will be empty upon startup but it will work nicely in multi-user this way. :wink2:

[LaRetta]

Hi Mark,

I realized this will break on Invoice Numbers that start with 1 so someday you might be in trouble with it. Your script should enter a 0 (to show only unassigned invoices) or a number larger than your greatest invoice.

So script could be: If (gInvoiced ; 0 ; Last(Bookings::InvoiceID_fk)+ 1 )

Sorry I didn't catch that earlier. And others may have a different idea also.

L

[Mark Ballard]

Hi LaRetta,

Well thank you for all your advice and we're very nearly there! The solution you've provided in your last two posts seems to work apart from the following two problems:

1) The "New Booking" Button now just jumps to the last record in the portal. I assume that this is because with the relationship link you've suggested, there is not an "allow creation of records..." option.

2) I just made an invoice No.1010 just to make sure that you're theory worked but although the invoice has been created that booking now doesn't appear in the portal!?

I'm sorry this is taking up so much of your time, but i do appreciate all your help. Just make sure you're having a few days away when i start on payments linked to invoices and assigning part payments to different invoices!! }:(

Patient_7.fp7.zip

[LaRetta]

Hi Mark,

You won't be able to create new records in this portal because it isn't based upon =. You will need to script it. I assure you that your Users won't notice a thing. Right-Click New Record and add this script parameter: Patients::PatientID_pk

Then modify your New Record script thus:

Freeze Window

Go To Layout [ Bookings]

New Record/Request

Set Field [ Bookings::PatientID_fk ; Get(ScriptParameter) ]

Commit Records/Requests

Go To Layout [ Patients ]

Go To Portal Row [Last]

Go To Field [ Booking Invoices::Date ]

But before you test it, change the sort on the portal to the new Booking Invoices Booking_Date also. It still will point to Bookings::BookingDate. Be sure any scripts accessing this portal are changed to the correct TO name or they will fail.

"although the invoice has been created that booking now doesn't appear in the portal!?"

It does on mine. Did you also put the PatientID in? [color:blue][Correction] Nope. It breaks. I will find out why.

Don't ever worry about bothering someone. If I didn't want to respond or be here; I wouldn't. FM Forums feeds me.

LaRetta

Edited March 23, 2006 by Guest

[LaRetta]

Got it. We needed to change the Show Uninvoiced Booking script to the new calculation. You hadn't changed it in your script and I hadn't changed the Specify Button on my copy (so my clicking the button broke it). I was originally manually entering the data in the global for testing. I KNEW it worked when I tested it.

UPDATE: And since your invoice numbers aren't auto serials, you won't want to use LAST() because the invoice numbers are assigned out of 'creation' sequence. So change the script calculation to:

If ( Patients::gInvoiced ; 0 ;Max ( Bookings::InvoiceID_fk ) + 1 )

L

[Mark Ballard]

Hi again LaRetta,

I've done the penultimate post of yours and all's good but i couldn't get the last script you suggested to work. I think there's something wrong in the syntax of it??

UPDATE: And since your invoice numbers aren't auto serials, you won't want to use LAST() because the invoice numbers are assigned out of 'creation' sequence. So change the script calculation to:

If ( Patients::gInvoiced ; 0 ;Max ( Bookings::InvoiceID_fk ) + 1 )

It does now switch between bookings that are invoiced and those that are not, and the new booking button is all good again. The only remaining thing is that the invoice 1010 that i did as a test still doesn't appear. Is that to do with the script that needs altering?

To be honest I don't totally understand what you've done, i'm just happy it's nearly all working!

Attached below is latest version

Many Thanx

Mark

Patient_7a.fp7.zip

[LaRetta]

Mark, which script are you using in Show Uninvoiced Bookings? This is the script which must be changed. Everything else looks fine. But the Show Uninvoiced with my name is still the old toggle script. This is what needs to be changed to the Max() calc I posted. :wink2:

[LaRetta]

Here - I changed it for you (attached). Your button says Show Uninvoiced. It is the Script called Show Uninvoiced LaRetta. It was still the old toggle. I've changed it to the max. Now it works. ^)

And you still haven't corrected the sort. It still shows Bookings::Booking_Date. Delete that and add the Booking Date again - descending. :wink2:

Patient_7.zip

[Mark Ballard]

LaRetta Thank you so much for your persistance.

I had misunderstood what i was meant to change about that script, hence why it wouldn't let me do what i was trying to do!

It all works perfectly now.

Well I'm nearly all there now just the payments side to sort out! I think you've done more than your fair share so far but would you be prepared to guide me through that as well. Please, please don't feel obligated.

I'm a Sound Engineer by trade and am more used to going "One, Two," etc etc. We never get to three because it means we have to lift! While i'm pretty good at most things on my Mac, I am learning all this from scratch to build this system for my Wife's Osteopathic Practice. We looked at several ready made products that were all either too expensive or way too complex, or both!

I have really tried hard with this to keep it as simple as is possible and avoid having lots of layouts, hence trying to keep it all really within the Patients Layout and a couple of portals.

With the payments, I need to be able to assign each payment to one or several invoices, and will need to assign an amount of each payment to each invoice. I'm assuming i'll need a join table to do this between Payments and Invoices?

What would be really good and to make life easier to input data would be that as you made a payment, you were given a choice of any outstanding invoices for that patient.

I'm also assuming that i need a method to auto calculate the outstanding balance of each invoice so that i can then do searches for any outstanding amounts etc.

If the last three paragraphs have filled you with a sense of dread, I understand But if you feel like helping I'd appreciate any pointers before i start I'm only just recovering from the last few days!

Many Many Thanx

Mark B

[LaRetta]

If the last three paragraphs have filled you with a sense of dread, I understand.

No, actually they excite me but I'm on a current crunch. If you get stuck and post a new thread, I'll respond if I can though! You might also search here on Forums - this issue comes up quite frequently.

LaRetta :wink2:

[Mark Ballard]

Hi LaRetta,

I have tried to modify your script to do the same job for showing invoices with anything other than a zero balance in a third Portal. Sadly it looks like i didn't properly understand how your solution worked, I was just very happy to see it work!

Could you explain to me how your script works in somewhat layman terms so that i properly understand it and will be able to change the new script to do what i need.

Many Thanx

Mark

Patient_9.fp7.zip

[LaRetta]

Hi Mark,

You have some issues here to consider ...

To display Invoices (in portal) with outstanding balances (which must be unstored), you can not use the balance fields as the child (right-key) in a relationship. You must instead perform some ACTION which can be identified by Invoices (identification equates to indexable) that a payment was/is being made. This is usually handled in one of two ways: 1) Change the Balance – each time a payment is made, perform math (via script) on the related invoice (subtracting payment) or 2) PIF (Paid In Full flag). I prefer using PIF. Why write (and perform math) on a balance (sometimes many times) when FM already knows the true balance (via cOutstanding). It is far simpler that, when payment script is finished, script does this:

Commit Records/Requests

Set Field [ InvoiceBeingPaid::PIF ; not InvoiceBeingPaid::cOutstanding ]

The only way I know to ensure that Receivables is handled properly is solid scripting which tightly monitors the User, the remaining Check balance and the Invoice balance after EACH payment application. You might have one payment against many Invoices and an Invoice with multiple payments. You need tight control mechanisms. A portal of payments just doesn’t cut it - a User can get away too easily. I prefer a small payment window in which I trap the User with only one escape route – my button. Then I can make sure everything is finished correctly.

You will bump up against conflicting theories ... how to index a nonindexable field, flagging records, process stability (user action on low end vs. tightly script event-trigger plugin on the other) and a myriad of other considerations. I’m on tight commitments at present but if you would like a sample file, I can put one together in a few days when time constraints lessen - just let me know. In the meantime, maybe others can make suggestions as well. Variables can help you a lot here as well ...

LaRetta :wink2:

[Mark Ballard]

Hi LaRetta,

Sorry for the delayed response again. I've been away with work. All of what you have said above makes sense. Presumably the PIF flag can be set automatically whenever the balance reaches zero as part of the payment script?

If and when you have time an example would be great but not to worry if you're too busy, I'll battle on!

The original fix you did for toggling my bookings portal, still confuses me a little, can you do a very brief explanation of what's actually going on! Sorry, it just phazes me.

Many Many Thanx