I've totally lost myself!

[LaRetta]

Hi everyone!

I have a Client dB which lacks critical information to generate a billing. I want to create a report per Therapist, giving them a *list* of missing information (errors) on each of their Clients, so they can *fill in* the required information on the report, as follows:

ClientName

Missing Medicaid # ________________

Missing Diagnosis ______________

Invalid Social Security #______________

At first, I started to write a calc, but I don't like the thought of using up resources (particularly adding the "message text" portion (see above), so I instead created a dB called Errors. This Error dB has an ErrorID (1) and ErrorDescription (Missing Medicaid #). Here's where I get lost!! I want to relate the two dBs on the ErrorID using a calc (?) in main (Clients) as a multi-key field, which would trap the appropriate ErrorIDs.

But I

[Ugo DI LUCA]

Well, this seems to me a good idea to find this kinds of errors

I will wait for other answers to see how to implement a Error db for this, but if you have these 3 errors to catch, you could give each error an error ID (text), and then you should try this.

Error_Id = Case(isEmpty(Medicaid#);"A1" &"

[LaRetta]

Hi!

Exactly what I want except I don't want to waste 'calc-energy' in typing the complete error message within the calc - that's why I created the Error dB. There will be 18-25 fields across three dBs, so there will be that many error messages.

From your example, I created the following test cError.Key (text):

Case(IsEmpty(Medicaid#), "1" &"

[Ugo DI LUCA]

In addition to my first answer, I went and tested the solution. As I supposed, but I do not know why, this calc wouldn't work for number fields.

I will still wait for anothr answer to your post, as these solutions could be a great help for me too.

[Ugo DI LUCA]

So you've got 18-25 records (2 digits before the "

[LaRetta]

Well, I've got the relationship with Errors working perfectly. cError.Key calc (text) fills with each ErrorID on a new line according to the errors generated. However, when I try to create a report I'm running into problems. When I, from Clients, place the cError.Key in the body, it only displays the first number (but if I click on the field, there are two). And, when I insert the Error::Description field, it only lists the first error 'message.'

Now I'll be disappointed if I find out that I still need to create calcs to combine or split them Would it be by using ValueListItems (dbname, valuelist)?? I like the theory of multi-keys but they're confusing on how to display them

LaRetta

[djgogi]

Instead of putting Error::Description field on layout (which will display only data from first related record), create an value list "ErrorList" based on relationship Error (the above relationship) as use only related values from field "DescriptionWithNumber" (this is simply "nError & description" calculated field in DB "Errors".

Now, as always when you need to bring relational data from more than one record into master file, define an calculated (unstored) field (in master)

currentErrors=ValueListItems(Status(CurrentFileName), "ErrorList")

Put this field on your layout report.

If you wish to convert it to single line, use:

currentErrors=Substitute(ValueListItems(Status(CurrentFileName), "ErrorList"),"

[Ugo DI LUCA]

Just about the last calculation you posted, as it could help me for another file.

Does this works for any value list or only for this one as it has "

[LaRetta]

Hi DJ

Ohhh, that makes total sense, thank you! Ummm, I was playing around with ValueListItems (dbname, valuelist) but the silly thing wouldn't let me change dbname to Client.fp5. No matter how I listed it, "Client", or "Client.fp5", or Client, or Client.fp5 it kept giving me an error message. How in the world can I select a dbname to replace part of a function? Usually, that portion to replace highlights itself and I select the appropriate field and double-click and it just replaces it. Oh, not so with dbname.

I see you used Status(CurrentFileName) instead, and I understand using a Status function is usually best, but if I HAD to use the above function, how do I specifiy the dbname?

Thanks again for teaching me another vital piece to this FM puzzle!

LaRetta

[Ugo DI LUCA]

"Client" should work if it is your filename. It works for me on a Mac.

[LaRetta]

Well for goodness sakes

It just did for me too! I'll bet I was typing "Clients," uhh, well, I had been up most the night on this stuff Thanks for straightening me out!

YO DJ!! Did you catch that my question was answered?

LaRetta