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?
FileMaker Pro

Avatar of undefined
Last Comment
Cruizectrl

8/22/2022 - Mon
lesouef

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
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.
lesouef

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
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Cruizectrl

ASKER
Oh god.. So I have to reconstruct the layouts as a whole?
lesouef

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
Cruizectrl

ASKER
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
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
lesouef

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.
Cruizectrl

ASKER
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.
lesouef

yes, but I can't what's wrong without the script content...
Your help has saved me hundreds of hours of internet surfing.
fblack61
Cruizectrl

ASKER
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.
lesouef

can use a custom dialog box in yr script?
Cruizectrl

ASKER
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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
ASKER CERTIFIED SOLUTION
lesouef

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Cruizectrl

ASKER
Alright.. And then the question is, how do I get a find/replace to work on selected field when the field is a checkerbox.
lesouef

manually, you can't, you must use a script, which will prompt for search and repalce value for instance using a custom dialog box
Cruizectrl

ASKER
Alright.. I guess it was worth shot.. More scripting =(.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23