Managing a Transaction Table

[sollasollew]

Spent some quality time learning a bit about Alpha5 yesterday. So far, so good. I would like to create a transaction table that records status changes for clients in an HIV/AIDS program. Each client will have several things he/she can have a status change about.

I would like to create a single table for all of this. After selecting a client, users would select the status being changed from a drop down. Then, and here's the rub, only the values relevant for the status being changed, would be available. For example, if the client had a new housing status, the worker would select 'New Housing Status' from the 'Status Type' field and the 'New Status' field would include only things like "Became homeless", "Living in shelter", etc.

I realize first that theses choices would have to be fed from a table, this is preferred for a number of reasons. Question is, can you get the values in drop down 2 to change, based on the values in drop down 1? Is there an example of this in the help or in AlphaSports?

On another note, there are also certain numerical scores that will change for each client. Viral load is one, score on a psychiatric assessment is another. I can provide a separate score table and form set up to handle a numerical value. Question is, each assessment has a different range of possible scores. The viral load score might vary between 0 and 1, whereas the psychiatric measure might vary between 10 and 50. It would be good to be able to trap for out-of-range entries but the range will change for each type of assessment. Any clues?

Finally, does this approach, using a small number of transaction tables make sense? Finally, finally, will this work as well with a deskop versus and online application.

Thanks much.

[Keith Hubert]

Re: Managing a Transaction Table

Hi Eric,

Welcome to the world of Alpha.

You have asked a number of questions here, the best thing to remember here is that it is easier to reply to single questions, as there could be a number of answers and the topic needs to remain coherent.

can you get the values in drop down 2 to change, based on the values in drop down 1?

The anser is Yes; This would be done with a filter in the lookup.

Question is, each assessment has a different range of possible scores. The viral load score might vary between 0 and 1, whereas the psychiatric measure might vary between 10 and 50. It would be good to be able to trap for out-of-range entries but the range will change for each type of assessment. Any clues?

Conditional objects come to mind, where you would have different layers with different score methods for different conditions.

does this approach, using a small number of transaction tables make sense?

Not quite, but you might need a number of small tables for the lookups.

will this work as well with a deskop versus and online application

Very simply, Yes, but the setup for web pages is not the same as desktop.

[Tom Cone Jr]

Re: Managing a Transaction Table

Eric, there's a difference between recording the present status of something, and recording the changes to that status over time. Which are you asking about here?

[sollasollew]

Re: Managing a Transaction Table

My thinking was the present status of something is, for all intents and purposes, the same as the last change. In this case, we need to record changes in status. The current would be the status at the most recent date.

Eric

[sollasollew]

Re: Managing a Transaction Table

My thinking was the present status of something is, for all intents and purposes, the same as the last change. In this case, we need to record changes in status. The current would be the status at the most recent date.

Erc

[sollasollew]

Re: Managing a Transaction Table

Thanks much. Filter on the lookup it is. I will explore the help on this topic. I don't believe Access, where I have some familiarity, can do this. At least not very easily.

Eric

[Mike Wilson]

Re: Managing a Transaction Table

Eric,
In line with what Tom wrote, it is not clear to me what your goal is ultimately. With what you wrote, it actually sounds like there would only be one status field that holds values for many different status types so that each record would have data relative to only the single status condition... say viral load.... and have nothing regarding housing condition. But what if two or more status items changed? Does that now constitute two or more records entered for that client?

Or are you trying to have one record for each client with the many fields holding current data (viral load, housing status, employment, etc ) and the process is one of updating the fields of a single client record, and not having historical values as separate records? Or duplicating the entire record with changes in the field values for the changes in statuses so there is a historical record of all the changes?

The approach to these scenerios is vastly different.

[Tom Cone Jr]

Re: Managing a Transaction Table

Eric, Alpha Five includes rudimentary change log or audit trail capabilities. Check the help file topic "Using the Audit Trail Feature". A more complete approach is described in detail in Dr. Wayne's book "Xbasic for Everyone".

[martinwcole]

Re: Managing a Transaction Table

You can set a field as a cascading lookup - its like a tree. See image.
In field rules, at the records onsaverecord event, you could write the new value to a separate transaction table that might look like or similar to this:

clientid, newvalue, transdate, transtime, changedby, computername

The table need not be part of the current set for the current form, but could be viewed from a button bringing up a different form, filtered on that client.

This would likely require some xbasic.

[sollasollew]

Re: Managing a Transaction Table

You can set a field as a cascading lookup - its like a tree. See image.
In field rules, at the records onsaverecord event, you could write the new value to a separate transaction table that might look like or similar to this:

clientid, newvalue, transdate, transtime, changedby, computername

The table need not be part of the current set for the current form, but could be viewed from a button bringing up a different form, filtered on that client.

This would likely require some xbasic.

Ouch! This is al,most what we'd like to do. There would be one more field however- statusItem. StatusItem would hold the 'subject' of the status, say housing status or mental status:

001 housingstatus homeless 5-1-08
001 mentalhealth psychotic 5-15-08
001 housingstatus group home 6-15-08
002 mentalhealth depress 5-5-08

With this table I can get the surrent status and see the status changes over time. The issue is that 'homeless' is only relevant for housingstatus whereas psychotic is only relevant for mental health. The form needs to feed the right choices to a drop down, depending upon what status is selected in the prior drop down. Unfortunately I'm away on business right now and can't try Keith's idea on this machine.

Eric

[Mike Wilson]

Re: Managing a Transaction Table

Eric,
Wonder if this might help as a solution. Althought this example has the classic Browse refresh issue (I really wish this could get resolved!!!).

[sollasollew]

Re: Managing a Transaction Table

The problem with the cascading is that it will only fill the status and not the status type field. This is potentially workable hopwever. I will explore some more.
Eric

[sollasollew]

Re: Managing a Transaction Table

Great quote in your sig file.

Thanks so much for building this example for me. It does exactly what I want. Couple of questions :

-Is there a way for the user to manually refresh the form?
-More vexing... I see you have an update_status() function as the onPush action of the add button. Where does the form that appears come from and how does it know what table to update? Did you code that or was the code written by a genie or something?

Thanks much.

Eric

[Mike Wilson]

Re: Managing a Transaction Table

Eric,
Sorry I didn't get back to you sooner, I was away for the weekend.

The update_status() is a user defined function (UDF) that I wrote, not by action scripting, although I use the scripting genie often within script writing. This code is xbasic and xdialog. What you see come up is not a Form but is an xdialog that is generated within the code of the update_status() function. I "borrowed" this xdialog script from someone who posted an example on the message board a little over a year ago and have used it for just this purpose in several applications. I should, by all rights reference who that is, but I didn't register it. I like it very much, although I can't take credit for it. It uses a very mainstream A5 function - table.external_record_content_get(). This retrieves a filtered or unfiltered values of one field from a table in a list format. The script then uses each value in this primary list as an element in 2 ways. One is as an element in a name for a variable that is dynamically generated for each value in the primary list (using the very powerful eval() and evaluate_template() functions), and secondly as a variable to generate a second list that by it use links it to the value of the primary list, within the filter component of the table.external_record_content_get() function.

Both the primary and seconday lists get displayed in the xdialog. As the primary list item is chosen, it calls the variable associated with it to display in the secondary list. I will have to be honest with you that I know what it does and have to this point gained some insight into its functionality, but I haven't the complete insight or knowledge to completely reproduce it from scratch. I have to give thanks to someone who posted it, very thankful for it, and I modify it as needed with my growing learning of xdialog, learning each time I use and change it for my next use. I am however neither the author nor do I have the skill to be the author of that xdialog. But I thought it would fit well for your needs, so I threw it out there for you to see it work as a solution to what you were describing your needs to be.

OK, enough said at this point. I know what I have writen might be hyrogliphics for you, as parts of this code is for me, but I threw this out for you to realize your needs are achievable. Let me know if there is anything more I can answer.

[sollasollew]

Re: Managing a Transaction Table

Gotcha, more or less. The question with this function is to figure out what tables and field names it needs and where to place them. BTW, what is @BROWSE48976FA2? I click on it and get an error message.

Eric