Search Form Question

[DLATSHAW]

I have a database with a set containing a few tables. One is Head of Household and another is Family Members.

When a new client comes in and gives their ssn we need to be able to search both Head of Household and Family Members to see if the client has received benefits as either a HOH or family member of someone else.

I've created a query that will pull up every instance of a ssn in either of those tables. But the part I'm stuck on is how to turn that into a form.

I want the employee to be able to enter the ssn and click "find". Then a list of all instances of that ssn should show up. They should then be able to click one of those instances and either edit a current record using a form I have, or create a new record for that client, bringing forward some information like address, ssn, dob, etc into that same form.

If anyone would like to look at what I have I can upload a copy of the database with bogus data.

Thanks!

[Al Buchholz]

Re: Search Form Question

Dan

That can be scripted with a little advanced xbasic scripting and either decisions that you can map or allow the user to make based on what id found.

For instance, if a ssn is not found, does the person become a head of household or do you enter another ssn for a family member who may or may not be a HOH?

Then you can pass a variable with the ssn to the appropriate form and put the form into find or enter mode....

[MoGrace]

Re: Search Form Question

I would construct the tables differently, using one table to record all people with an unique ID (SSN?) and other fields to tie them together. Then you can do this initial search for the SSN using an xdialog which in turn can open the form you want to see based on different sets.

[DLATSHAW]

Re: Search Form Question

It may be difficult to change the table structure at this point. Everyone does have a unique ID, but I can not use SSN. The old data I need to bring forward does not have consistent and correct SSNs, also, they need to be able to change the SSN when someone gives them a false or incorrect number.

So from both the answers it would appear I need to learn xBasic? Or is there an Alpha Genie that will build it for me? Not sure if I have enough time to learn another language before this project needs to be complete.

[preston2]

Re: Search Form Question

I've created a query that will pull up every instance of a ssn in either of those tables. But the part I'm stuck on is how to turn that into a form.

I want the employee to be able to enter the ssn and click "find". Then a list of all instances of that ssn should show up. They should then be able to click one of those instances and either edit a current record using a form I have, or create a new record for that client, bringing forward some information like address, ssn, dob, etc into that same form.

If anyone would like to look at what I have I can upload a copy of the database with bogus data.

Thanks!

This is similar to what I do for customer estimates and invoices.



Without seeing your database structure it is hard to suggest the best way to do what you want but I am sure it can.

Both of the above use embedded browse that when a record(line) in the browse is clicked it sets a calculated fields value to that record number which can then be used in a query fired with a button to open a form to edit that record.

What I think you are looking for is something like this but with a button that would then open a form to edit the SSN.

[DLATSHAW]

Re: Search Form Question

Both of those examples would be very helpful. I think the first one is more in line with my immediate need, but the second one I like for a future update.

I've attached a sample of the database. The "Intakes" form is where the bulk of their work is done. The errors it reports when opening are for fields I did not update yet. The "Intakes" form is where the search should end up.

If anyone gets a chance to look at it maybe look at the "EMPLOYMENT" tab. The button there that says "Save and New Employer" should take the data from the Employer fields and add it to the browse. But currently it saves the entire record, not just one tab. This would help me with other portions of the data input. I'll need to do similar for income sources and employment for all household members.

I've looked through Alpha Sports and some of the other example code but can't find what I'm looking for. So if anyone has examples that'd be great.

Cheers!
Dan

[preston2]

Re: Search Form Question

Quick take on what you have.

Head of House hold is stored in the general table.
Family Members are stored in the Family table.

There is only one field for the SSN and that is stored in the General table so a Family Member cannot have a different SSN entered and thus you cannot search to see if the SSN was used for a Head of Household or a Family Member. You would need a field in the family table for storing the Family Members SSN and thus would be able to see if that SSN was used more than once and if it belonged to the HOH or FM.

Also family->FAM_MI is Numeric so if that is supposed to be "Middle Initial" then you can only enter numbers and not Characters.

[DLATSHAW]

Re: Search Form Question

There is a fam_ssn in family table. The family member SSN would go into that field. If you look under Operations I have a query called "ssn search on set" that searches both general and family.

Thanks for catching the FAM_MI problem! I was going to add a FAM_ID so that I can keep multiple iterations of a family member. Confused it with FAM_MI and never fixed it.

[preston2]

Re: Search Form Question

There is a fam_ssn in family table. The family member SSN would go into that field. If you look under Operations I have a query called "ssn search on set" that searches both general and family.

But it is not on the Household tab that I can find so it would never get a SSN entered into it.

[Al Buchholz]

Re: Search Form Question

It may be difficult to change the table structure at this point. Everyone does have a unique ID, but I can not use SSN. The old data I need to bring forward does not have consistent and correct SSNs, also, they need to be able to change the SSN when someone gives them a false or incorrect number.

So from both the answers it would appear I need to learn xBasic? Or is there an Alpha Genie that will build it for me? Not sure if I have enough time to learn another language before this project needs to be complete.

Having designed and supported a paternity testing database ( in Alpha 4 no less) I agree completely with Robin's suggestion. One People table, other tables for relationships.

SSNs are not unique. No government operation could handle that much data clarity. They should never be used a linkages. Just identifiers to (maybe) find someone.

The paternity system was initially for blood testing results, then came DNA and the possibilities grew exponentially. Then the adopted children looking for parents.... It took the one table approach to make it work.

It would have been a massive redesign/conversion later one. Lucky at least on that system.

[DLATSHAW]

Re: Search Form Question

But it is not on the Household tab that I can find so it would never get a SSN entered into it.

You are correct, I misunderstood. I thought you meant table, my mistake.

If you look in the browse you can see it there. I've just been entering data in the browse as needed for testing.

[DLATSHAW]

Re: Search Form Question

Having designed and supported a paternity testing database ( in Alpha 4 no less) I agree completely with Robin's suggestion. One People table, other tables for relationships.

SSNs are not unique. No government operation could handle that much data clarity. They should never be used a linkages. Just identifiers to (maybe) find someone.

The paternity system was initially for blood testing results, then came DNA and the possibilities grew exponentially. Then the adopted children looking for parents.... It took the one table approach to make it work.

It would have been a massive redesign/conversion later one. Lucky at least on that system.

This database started life in Alpha5v4. There was one table with everything. You could only input 7 family members because there was a fam1, fam2, fam3, etc in the table. When a person would come and have an 8th family member on their third visit they would have to overwrite previous visit data and delete a family member.

I'm sorry but I do not understand the advantage of having all people in one table, but I'm open to being proved wrong. They need to be able to have information for every visit and know who was in the household for that visit. Then later see who was in the house before, and who is in now. And when someone moves into another house they want to know that person previously lived in a household that received help from this office.

I've done similar setups with php/MySQL and would do a LEFT JOIN to get the info I need. Then put it in a variable to be posted back to MySQL later. I have the query written that works:

 (( var->AskC_enter_ssn $ General->clssn ) )  .or. (( var->AskC_enter_ssn $ Family->fam_ssn ) )

But don't understand how to get those results to allow me to chose which result to post to the form.

Thanks to all so far for your time!

[MoGrace]

Re: Search Form Question

Hi Dan,
I think you misunderstood both me and Al. You don't need additional fields like fam1, fam2-8, etc to include other family members, only a 'family' link field for each individual record. So if you are setting up a head of household member and want to add some children you need a 'master' family id, which is passed to each child record you add - using the same table. Tables in sets can be linked to themselves by using an alias...

[DLATSHAW]

Re: Search Form Question

I understood that you both meant using the same table, and adding a new row for each family member, but thanks for clarifying on the id. What I was unable to communicate is that the original table, which is not included in what I uploaded, had those separate columns for fam1~7. So the original table could only have 7 members. My fix was to have separate tables where family members were linked to the HOH by an ID. Which is similar to what has been suggested, save they are in different tables instead of one table.

I'll have time today to work on this project. I'll take into consideration what has been said here. But I'm still unclear on some of the mechanics of alpha forms. I'm having trouble going from php to alpha.

[MoGrace]

Re: Search Form Question

I have been working on this app with Dan and just realized that if he wants to see DIFFERENT family members linked to a particular visit from the HOH Client then an intermediary table is needed to somehow link the family member to both the Client and optionally to an Intake, thus creating a many to many relationship. Initially I thought a DATE IN field might help here, but multiple visits creates multiple dates and the family member table ought not to be duplicated. So how to create a set that would allow multiple intakes linked to one Client and also multiple family members linked to the client and each intake? If the user is presented with a list of all family members for the client, then he needs to pick just the ones to be linked to that visit.

Any ideas on what that link would be? Or how to structure the set?

Here is my imaginary scenario:

On 12/01/2016 Donald Duck (the HOH Client) and his wife Daisy come in for assistance
1. CLient table: Add Donald and get a new Client_id & Member_ID
2. Member table: Add both Donald and Daisy using same Client_id but each has a unique Member_id
3. Intake_Hdr table: Create a new Intake_ID for this date and Client_ID that links 1:N to Member by Client_id to show both Donald and Daisy listed as Members

On 12/15/2016 Donald, Daisy and their 3 nephews, Dewey, Huey and Louie who have come to live with them , come in for additional services
Repeat step 2 for each nephew and Step 3 for the new Intake date but somehow select all 5 to show for this Intake only without it affecting the previous one. ?