Link to home
Start Free TrialLog in
Avatar of Cruizectrl
Cruizectrl

asked on

Record straight and reverse assignments

I have a table with records for documents and checkboxes at the bottom that list all active employees.  for each record I can assign certain employees to each record.

I would like to somehow reverse this now to be able to do the same but flip flop the assignment to show each employee and a list of documents that can be assigned.  This would need to be dynamic in a way so when I have for example

I check employee one for document 2 4 6

when I look into the employee record I see

Employee one
doc1
doc2 x
doc3
doc4 x
doc5
doc6 x

Of course this would need to reflect in the other side.

Would this need to be done with scripting?
Avatar of Member_2_908359
Member_2_908359
Flag of France image

no, but you need a 3rd table to store employee-doc couples.
this table would only have 2 field, employeeID and docID used to link to each other table
Viewed from employees, you'll see available docs from the 3rd table
Viewed from docs, you'll also see employees using them.
Each assignment should create a new line in that 3rd table
Avatar of Cruizectrl
Cruizectrl

ASKER

How would I do this all through checkboxes?

I have 70 documents with a checkbox at the bottom to assign employees.  I would like to reflect this the other way with the Employee and checkboxes for all the documents.  But if changes are made to one side.  It should change in the other side as well.
Modify the checkboxes as a button which will trigger the script which will create the new record in the 3rd table and make the checkbox field a calculated field is there is a related record in the 3rd table for the current employee. Same for the doc table
Oh god.. So I have to reconstruct the layouts as a whole?
certainly if you need to setup checkboxes as buttons in 70 layouts.
there may be an easier way by using the event plug-in from softs4humans.com;
it allows to trigger a script upon field data change: using it, you could trigger a script which creates the new record in the 3rd table when you check the box or delete the record when you uncheck it by simply checking the checkbox field value in the script to decide wheter you should create or delete a record in the 3r table.
http://www.softs4humans.com/FMPro_Plugins.html
OI!

I'm just working with 2 layouts and two tables at the moment.

One holds the employees and one holds the document records.

I have an assignment checkbox field, that is listed from the employees, in each record in the documents table.

so again  I would have

Document 1
Emp1 x
Emp2
Emp3 x

Document 2
Emp1 x
Emp2 x
Emp3

Document 2
Emp1
Emp2 x
Emp3 x

But then when I look at the employee record I should see this (and be able to change as well where the changes would reflect in the other table..)

Emp1
Document 1 x
Document 2 x
Document 3
that all depends how your tables are linked, but I am afraid that's gonna end up with links all interleaved!
every user must be link to 3 different occurences of the doc table and vice versa. this is already 6 relationships for 3 docs and 3 people...
Anyway, if you end up wiht more than 3 users and documents, a 3rd table will be neccessary.
Is there a possibility to add and remove items from a checkerbox field with scripting?  Each time I attempt it, it winds up clearing the field except for the selection I want to add.
yes, but I can't what's wrong without the script content...
I'm just trying to play around with a find/replace script step but since i cant "enter" a checkerbox field, it wont let me limit to just one field.
can use a custom dialog box in yr script?
I dont understand.

I have a field where I can check off about 80 people (in the background its by ID number).  I tried making a script to check and see if a certain employee number existed.  If not, It would add him to the list.  And if hes present when the script runs, remove him from the list.  I only know how to do this with a find/replace "60" to ""  But I'm wondering if I have in the list
160
60
and I try this, it would effect both of them.
ASKER CERTIFIED SOLUTION
Avatar of Member_2_908359
Member_2_908359
Flag of France image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Alright.. And then the question is, how do I get a find/replace to work on selected field when the field is a checkerbox.
manually, you can't, you must use a script, which will prompt for search and repalce value for instance using a custom dialog box
Alright.. I guess it was worth shot.. More scripting =(.