Notifications
Clear all
Topic starter
Where is the table/field of who imported an external attachment into a document. I suspect this is one of the LL3 tables, but I can't find it. Any help would be appreciated.
Posted : August 25, 2017 12:15 pm
I am assuming you mean an external attachment imported via link logic via some sort of document management system. If so, there is not really a person involved in that process as it is just a file being picked up by linklogic. The data from the file should be imported with it and be displayed in the chart and usually has the person who imported it. If it is not, check the linklogic save directories for the day/time it was imported and see if the initial file has additional information.
Your better bet might actually be to browse to the file itself with windows explorer using the UNC path and switch to details view. Right click on one of the column headers (ie Filename, date, etc) and click "More' and then scroll down in the list until you see owner and check that box and check it. That should tell you the name of the person who wrote the file to disk.
Posted : August 28, 2017 1:40 am
Topic starter
I very much appreciate your thoughtful response.
I needed to be able to run a report without any manual steps. In looking at the generated document for adding an external attachment, it includes the verbiage, "Imported By: name date-time". I linked the DOCUMENT to DOCDATA, filtered on HASEXTREF, parsed out the name from DATA in DOCDATA, then created an array of the names and incremented occurrences.
The description is considerably shorter than the design of the report. The execution is very slow as it must select on document dates and the database is huge.
Thanks again.
Posted : August 28, 2017 1:09 pm
I guess I misunderstood your initial question. I thought you were researching a particular document trying to track something down instead of trying to automate a report. I have used the text from docdata with the "Imported by:" many times to track down which users are importing documents improperly (without observations). So much that I ended up writing a function called "stringbetween" which literally finds the string between two other strings. The function has 3 values, input string (docdata.data) start string ("Imported By:") and end string (" on "). I ended up writing the Function for both Oracle (what we use for emr) and MSSQL. The oracle one is more robust with default values for the start and end strings so that you can leave them out and start/stop at the beginning/end.
To find the user who imported it the SQL looks something like this:
SELECT STRINGBETWEEN(DOCDATA.DATA,'Imported By: ', ' on ') IMPORTED_BY
FROM DOCDATA WHERE ...
Here is the code for MSSQL:
ALTER FUNCTION [dbo].[StringBetween] (@TEXT VARCHAR(MAX), @START VARCHAR(MAX), @END VARCHAR(MAX))
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @OUTPUT AS VARCHAR(MAX);
SELECT @OUTPUT = substring(@TEXT,
charindex(@START, @TEXT) + LEN(@START),
(charindex(@END, @TEXT, charindex(@START, @TEXT) + LEN(@START)) - (charindex(@START, @TEXT) + LEN(@START))));
RETURN @OUTPUT;
END;
Oracle:
create or replace FUNCTION STRINGBETWEEN (TEXT IN VARCHAR2, START_TEXT IN VARCHAR2 DEFAULT NULL, END_TEXT IN VARCHAR2 DEFAULT NULL)
RETURN VARCHAR2
IS OUTPUT VARCHAR2(4000);
BEGIN
CASE
WHEN START_TEXT IS NULL AND END_TEXT IS NULL THEN
SELECT TEXT INTO OUTPUT FROM DUAL;
WHEN START_TEXT IS NULL THEN
SELECT SUBSTR(
TEXT,
1,
(INSTR(TEXT, END_TEXT) -1)
)
INTO OUTPUT FROM DUAL;
WHEN END_TEXT IS NULL THEN
SELECT SUBSTR(
TEXT,
INSTR(TEXT, START_TEXT) + LENGTH(START_TEXT) --find the location of the start text and start after it
)
INTO OUTPUT FROM DUAL;
ELSE
SELECT SUBSTR(
TEXT,
INSTR(TEXT, START_TEXT) + LENGTH(START_TEXT), --find the location of the start text and start after it
(INSTR(TEXT, END_TEXT, INSTR(TEXT, START_TEXT) + LENGTH(START_TEXT)) - (INSTR(TEXT, START_TEXT) + LENGTH(START_TEXT))) --find the first occurance of the end text after the starttext
)
INTO OUTPUT FROM DUAL;
END CASE;
RETURN(OUTPUT);
END;
Posted : August 28, 2017 1:44 pm
One more thought. If your query is slow, even without including docdata, I find it helpful to read through the indexes in document to try and find one you can hit. If you are on MSSQL your mileage may vary but in oracle, I usually find that if I inner join the usrinfo table to document it grabs a different index and is much faster. I think the column I link it to is the usrid. Even if you don't use that column in your output it might speed it up.
Posted : August 28, 2017 1:50 pm
