I think that you are putting multiple items in one field of one record that should instead be placed inside fields of a related table and then linked to that one record instead. That would make for much simpler scripts and more flexible reporting options.
You can't use substitute to remove everyother line of text because it requires specific text values to match and replace with. There is no such specific value in your pasted text.
But a looping script could loop through text after you paste it and delete every other line of text. But I suspect that your database would be better designed if every Medication was a separate related record instead of listing multiple medications in a single text field.
The same is true for your second issue where Diabetes would be placed in one field and the "several lines of text" would be placed in either a second field of the same record or each line of text might be in a text field of a set of related records--one related record for each line of text. Hypertension would be placed in the same field as Diabetes, but in a different record...
I see where you are going with the design... I understand what you mean. In theory yes. But in the grand scheme of things for my purposes typing each diagnosis or medication (In this case of the patient's medical history) into a separate record is not necessary nor is it user friendly. (I am not able to import from the facility EHR, but I can copy/paste from it) I think the other post was about Medications and I am also copy/pasting the med list as well, My DB is for my progress notes only so no one else needs to search anything...I know this is not the "Developer" way to think.... and from database perspective is not the ideal database scenario. (When I first started this I did have a separate meds table etc and did enter each into a separate record but I needed it to all appear in a paragraph format anyway so I then created one field for the Diagnosis history and Meds and so on and it was much quicker to type a paragraph. 6 months ago, my main facility adopted an EHR, and the copy paste thing has really cut down the time I need to spend with redundant (but necessary) documentation.
I have created fields with check boxes to act as "sentence builders" and then have turned those fileds into paragraphs, and combined those paragraphed fields into and actual paragraph of text. My report (progress note) contains one field per category so "Past Medical History" has one calculation field that appears as Diabetes; Hypertension, Asthma.... etc. The Objective section contains one field also but that field is the result of adding many text calculation fields together... and so on....Again, for my purposes, typing what could be 30 different values into 30 different records and then doing the same for 20 different medications is way too time consuming. I'd still be there... (and is sort of "reinventing the wheel" because someone has already entered the individual data into their "un user friendly" EHR and it is what I am copy/pasting into my progress note) Since I go to several Facilities, I am using my own "wheel" ... my own DB specific to my practice. As a provider, I need only be aware of the patient's medical history, if I need to search that sort of thing I am searching for the entire progress note...
So I am approaching this project from a "user friendly" perspective. A few days ago I posted about a MaxCreationDate thing... you were so helpful with that (once you understood the method to my madness :) I found a way to bring multiple records (one field created as decribed above) as a checkbox set into one field of the main table (the report/progress note is based on) ... this bypasses all the slide sizeing issues with trying to print from portals... because for my purposes I needed to bring "found sets" of data from multiple tables into one report (and was told this could not be done as I had orginally hoped without a great deal of complicated advanced scripting). Anyway if I could get a script as initally described I could apply those steps to these fields as well and improve upon the appearance of the finished report as well as save some time in the process.
So... to make a long story longer... :)
Is the Loop Script you suggested extremely complicated? Is there a character for Tab Indent? Or now that you have a better picture of what I need to do is there some much easier way or worst of all, not possible?
typing each diagnosis or medication (In this case of the patient's medical history) into a separate record is not necessary nor is it user friendly.
Typing in data by hand seems to be the main concern you have over my suggested approach. This (typing in data) is not what I am suggesting and related records maybe more necessary than you currently think. A script can parse your pasted data into these separate, related records for you without any need to type the data.
I know this is not the "Developer" way to think....
There's a reason why it's not the "developer" way... It's usually a vey unweildy approach to use in the long run.
needed it to all appear in a paragraph format anyway
One of the reasons why I am suggesting this is that this is an easier way to get and manage that "paragraph format".
If you are goint to use a looping script, it would be better, in my opinion, to use that looping script to move the data into those related tables after doing your paste.
The looping script I mentioned for removing every other row is not terribly complicated, but does depend on the pasted data having a consistent format.
I'm getting more confused here so I'm not going to try to "convince" you, I'm just going to trust you on this. You have never let me down and from the looks of other posts no one else either :)
What tables/relationships should I create to enable the "looping" script to work. I have no problem arranging this in any way you suggest... Blank canvas!
There are 2 fields I was trying to manipulate with the calculations are both on the Encounter Table... MedicalHx and CurrentMeds.I populate them in the same way, I copy and paste a column of items into them. when the data is in the spreadsheet, I need to manually remove every other row or "item" for MedicalHx it is in a number (ICD 9 code) but the rows removed from the CurrentMeds field contain text.
Regarding the second issue, I will walk before I run, maybe if I understand what you're getting at with the first thing ... I can better understand the second.
Never take anybodies advice from this forum on blind faith. We aren't you and we have only a partial picture of your database and how you need to use it. Often, it's like peeking through the keyhole to the front door of a house and trying to figure out the color of the paint in the bathroom.
Bottom Line: you have to think over the advice and decide for yourself what to do with it.
Here's the looping script in two forms. Sometimes it makes sense to do the "quick fix" now and then transition to the long term fix that necessitates a database restructuring.
Set Variable [$Count ; value: ValueCount ( YourTable::YourTextField ) ]
Set variable [$K ; value: 1 ]
Set Variable [$MedList ; value: List ( $MedList ; GetValue ( YourTable::YourTextField ; $k ) ) ]
Set Variable [$K ; $K + 2 ]
Exit Loop If [ $K > $Count ]
Set Field [YourTable::YourTextField ; $MedList
Here's how a modified form of this script can put the resulting list in a set of related records. For the purpose of this example, I will assume that you have two tables with this relationship:
Patients::__pkPatientID = MedList::_fkPatientID
Make the text field into which you paste this text a text field with global storage. It can be defined in any table. I've used Patients for simplicity here.
Set Variable [$Count ; value: ValueCount ( Patients::gYourTextField ) ]
Set variable [$K ; value: 1 ]
Set variable [$ID ; Patients::__pkPatientID ]
Go to Layout ["MedList" (MedList)]
Set Field [MedList::_fkPatientID ; $ID]
Set Field [MedList::Medication ; GetValue ( patients::gYourTextField ; $k ) ]
Set Variable [$K ; $K + 2 ]
Exit Loop If [ $K > $Count ]
Go to layout [original layout]
The first example is exactly what I needed! I can't thank you enough! And your statement about "blind faith" is well taken as well as much appreciated. I always practice these examples/changes on a copy of the database and the when I get it in the format I need I recreate the steps in the master DB. I will repost the other part of my question separately hopefully in a clearer way.
Thanks Again Phil! You are a Filemaker Genius!