[[https://www.revelation.com/|Sign up on the Revelation Software website to have access to the most current content, and to be able to ask questions and get answers from the Revelation community]]


==== Using one table to get data from another?? (AREV Specific) ====
=== At 05 JAN 2005 02:27:19PM Linda M Grant wrote: ===
 
{{tag>"AREV Specific"}}
 
<QUOTE>
Is there any way that I can use one table that holds one record for each person (history data)to get multiple lines of data from another table??  Basically I have one table called "patient.data" with a patient number say 2503 - in the other table "weekly.data" that same patient 2503 may have 200 lines of data (2503*1, 2503*2 etc..). Is there anyway to get all data from the "weekly.data" table for a particular group of patients grabbed from the "patient.data" table where I would just have the 1st half of the key?  



OR I can also select the patients from the "weekly.data" table using a criteria but I would like to select the patient by the criteria and then grab all lines of data for that particular patient that fits the criteria.  So if I use a date to select the people I would like all data that goes with that person not just after the date I select (if that makes sense to anyone)



HELP!!! Is this impossible without doing a major programming project?
</QUOTE>
----

=== At 05 JAN 2005 03:49PM Wilhelm Schmitt wrote:  ===

<QUOTE>Linda,



Actually it is very easy to relate to foreign files.

Assuming a PATIENTS and a WEEKLY.DATA file with a common field "PATIENTID",  you would:



1. In PATIENTS add a multivalued symbolic containing the foreign keys of WEEKLY.DATA by either

a) BTREE.EXTRACTing directly from the corresponding index in WEEKLY.DATA for patient=2503 

(This option assumes that you have an indexed PATIENTID field in WEEKLY.DATA), or

b) by looping through (or using a for...next... statement) and accumulating the keys of WEEKLY.DATA for that 1 patient

(This option assumes that you use sequential keys for each patient-transaction.)

c) A relational index, pointing from PATIENTS to WEEKLY.DATA would also accomplish the same task. However, I prefer to stay away from relational indexes in AREV.



The values in this field (PATIENT_TX) would look like this:

2503*1

2503*2

2503*3 etc.





2. Add any number of additional multivalued symbolic fields in PATIENTS to extract particular values from the WEEKLY.DATA file, through formulas like this one:

@ans=xlate('WEEKLY.DATA',{PATIENT_TX},'NEXTDATE','X')



The new fields are then treated as any other field in the PATIENTS file, i.e. they may be used for display in windows or in a report.



Hope this helps.



Wilhelm

</QUOTE>


[[https://www.revelation.com/revweb/oecgi4p.php/O4W_HANDOFF?DESTN=O4W_RUN_FORM&INQID=NONWORKS_READ&SUMMARY=1&KEY=F81CD73AAADFB57D85256F80006ADF57|View this thread on the  forum...]]