Create New Records Based on Several Tables

[johngtatp]

This problem is still on paper for me and this involves xbasic beyond my thought limits and I was hoping for a big push.

Essentially I need to create a memo, the memo will have a one-to-many link which departments it is applicable to, could be one, could be five or so.

Once the memo is created I need to track who has to read this memo.

My thought is to create a record in a new table that has the employee number and the memo number and a logical field to state if they read it or not. My problem is the xbasic code to look at the one-to-many link to departments, determine the applicable departments, then find all the active employees (logical field in the employee table) for those departments, and write the record.

I have used xbasic to write very basic records but nothing that involves arrays of data like this and I could use a big learning push here. Thank you for your time in advance.

[Ted Giles]

Re: Create New Records Based on Several Tables

John, this is not so difficult if you track the "Read it" button.
Have a think about this.
I have a system which sends emails to team members when the task they were due to complete has not been reported as complete.
They keep getting regular reminders until the job is done or they reschedule. I use an OnTimer in a Form.
Now, if you have a table with the departments in it, linked to the current memo with a "read it" flag which captures the data and time and person logged in, you will have a decent level of control.

The deveil is in the detail, but off the top of my old grey head, I'd have something like this in mind;

A Table with Departments in it as you suggest. There will need to be different levels of application of the memo.
..Memo Date and Content Identifier or some other identifier - so Memo type 00123 goes to Marketing, All marketing staff, Production, Supervisors only.
....Department+Memo+Content Identifier+Read it flag and audit details.

OnTimer, I'd send either an email reminder where Read It = "", or have the Memo Reminder display in their Log-On screen service area.

So, suggest you identify the depts, the memo specifically, and a way of getting them to confirm with a tick box. If the memo is a report, you can put the tick on the reports so it's a simple click through for the user. Put it at the bottom though!

Finally, I wouldn't think you need any Xbasic, and most if not all could be done with Action Scripting but I'm sure someone will correct me if I'm wrong.

[Mike Wilson]

Re: Create New Records Based on Several Tables

I disagree Ted,
I have tackled this before and still do not have a firm opinion on the best way of doing this. See this thread. Not many takers. Difficult subject. But maybe my needs were more than what John needs.

http://msgboard.alphasoftware.com/al...highlight=read

[Ted Giles]

Re: Create New Records Based on Several Tables

Then I have obviously misunderstood.
In simple terms it is like getting a Read Receipt for an email.
Similar to a message that displays when a person is using software that isn't licensed.
I read the post link Mike but haven't looked at the zip yet. I'll do that over the weekend.

Edit:
On second thoughts I can't afford the electricity bill to build the documents. I'll try and put an example of what I mean together instead.
Edit 2:
Sorry Mike, I was working with V10.5 so that might be why the extract didn't work.

[johngtatp]

Re: Create New Records Based on Several Tables

Mike & Ted,

Thank you for your guidance here. I looked at the demo and this is a bit more complex but similar in functionality. My goal is as each user logs in they are taken to a browse on a form that has the abstract of each memo that they have been assigned to read and there will be several variables to act as filters for the memos such as a date range, category of memo, If they have read it or not etc. I need to be able to run reports to say if it has been read or not. I think I like the method 1 version of your demo as that is what I was thinking about.

Ted,
I do not understand where the record or logical is kept for each person reading a memo and how they are applied to each memo.

[Ted Giles]

Re: Create New Records Based on Several Tables

Summut like this.

[johngtatp]

Re: Create New Records Based on Several Tables

Ted,

First apologize for the delay. My wife gave birth the weekend after your post and I have been out a week. Your example is exactly what I am talking about. I added a few more people and added a "Department" to them with a Departments table. This is where I have my problem. When someone adds a memo, I need to capture what department the memo is applicable to. I am not sure the best way to do this multiple selection except to hard code it, but would rather use the departments table to populate a dynamic list to have the choice to enter many departments. Here is the second rub for me, Once the memo is created, how to then find all the associates who the memo is applicable to and create a record for each of them in the "read_it" table? Attached is the modified file with a departments table.

[johngtatp]

Re: Create New Records Based on Several Tables

I had a thought. What if you only create a child record if the person has read the memo. The question is can you easily filter/search if there is no child record?

[Mike Wilson]

Re: Create New Records Based on Several Tables

John,
The difficulty comes in when trying to have the combined capabilities to identify those assigned to read and then also needing to filter for memos that have or have not been read. Having gone through this before, I think the most straight forward way is to have a memo field in the connecting table that collects the user_id's when they sign off having read the memo. Attached is an example using your posted example.

[johngtatp]

Re: Create New Records Based on Several Tables

I like the method here. I have almost never worked with crlf() data before and this poses some challenges for me. What I envisioned is first having a green dot or red dot in the browse indicating if it is read or unread by the person. With the data in a memo field, would it be possible/practicle to build a calculated field to do this? Also, is it possible to check the memo field for a duplicate name entry and not write it if it exists? The way it shows now you have to select a dept to view the memos, could this be done via a search, to clairfy, I was thinking of having the users see all the memos and having various restrictors on top of the browse, i.e. a date range, created by, the department, and category( I wanted to add a category to the memo, i.e. policy or customer note etc.), as well as a body text search. I see the value in this being associates looking up old memos based on a small tidbit of data. Thanks for your thoughts and work on this.

[Mike Wilson]

Re: Create New Records Based on Several Tables

What I envisioned is first having a green dot or red dot in the browse indicating if it is read or unread by the person.

.
I don't know how many memo's you are talking about, but this indicates all memos, read and unread, would be displayed. I personally wouldn't want to be having to deal with read memos. Read them, sign off and move on. Have the capacity to filter for the read and unread so you can focus on the need.

With the data in a memo field, would it be possible/practicle to build a calculated field to do this?

Not sure what your thinking here. If you want a color background to appear in a field within a Browse based on the presence of the value of a field (in this case the userid being in the memo field, which means it's been logged as having been read), you highlight the Browse column, goto Properties > Other > Color, enable color expression and add color expression var->vg_userid $ memo_dept-> Readers.

Also, is it possible to check the memo field for a duplicate name entry and not write it if it exists?

Of course. Wrap the code in the log_as_read() UDF that writes to the userid to the table with:

 if vg_user !$ t.reader   ' add if id not in memo field
...write code here
end if

(be sure to include end if). This isn't needed if you prefilter the group to present those that are not read to begin with.

The way it shows now you have to select a dept to view the memos...

Not true. Push the Memo button on the main menu and you will see all the memos.

, could this be done via a search, to clairfy, I was thinking of having the users see all the memos and having various restrictors on top of the browse, i.e. a date range, created by, the department, and category( I wanted to add a category to the memo, i.e. policy or customer note etc.), as well as a body text search. I see the value in this being associates looking up old memos based on a small tidbit of data.

Sure, this can all be done. It's a challenge, so study the structure and ponder what you want to have happen and I'm sure you can work through some stuff.

[johngtatp]

Re: Create New Records Based on Several Tables

Thank you for your response and this seems doable. I need to be able to see all the memos as they are reviewed frequently. This system will be used as a pass-down log so that information from shift to shift is passed along and sometimes it needs to be re-read.

My last question is since I have never worked with dilmited fields, is how can I run a report to show me the memos and who has not read them? How should the filter look?

[Mike Wilson]

Re: Create New Records Based on Several Tables

The reason this is difficult is not that "who hasn't read this memo?" is the question, it is that it is a double tiered question with a different point of focus - "Who is supposed to have read this memo that hasn't ? And you have to also include a third tier of what memo's come into the question. All memos? Memo's from yesterday? For a particular department? It get's complicated very quickly.

[Ted Giles]

Re: Create New Records Based on Several Tables

Hi John, I've been away in the RV for a while. I think I understand the issue.
What would help here is a State Machine Diagram - this explains the state of the memo and response.
Can you document the process in Visio?

[Ted Giles]

Re: Create New Records Based on Several Tables

Ted,
First apologize for the delay. My wife gave birth the weekend after your post and I have been out a week.

Sorry John, many congratulations to you and your good lady - Boy or Girl?