Find Script based on multiple values

[James Patrick]

Hello,

 

I have been searching for answers to this problem but haven't had much luck.

 

I have 2 layouts based on the same table. I have a field called department on each layout that has a dropdown with the values A, E, P, C.

 

What I am trying to do is create a script, that triggers onlayoutenter in the layout setup, that shows only records with the value A and E on layout 1 and only values P and C on layout 2.

 

My script I was using was perform find based on the field dept = A, omit records with dept =  P.

 

This seems to work but I can't get it to work with multiple values.

 

Any help is greatly appreciated.

 

Thanks

 

James

 

[doughemi]

To do an "or" find (find records where condition 1 or condition 2 are true), use the New Record/Request command:

 

Enter find mode

Set Field[department; "A"]

New Record/Request

Set Field[department; "E"]

Perform Find

[James Patrick]

Thank you that works great. It was the OR that I was missing.

 

James

[James Patrick]

Hello this works great but I wonder if there is a slightly different approach. I have a script trigger that works on layout enter but the problem is when you navigate away from the two layouts and return the script triggers and you are on a different record to before. What I would like to achieve is the ability to navigate away and return to the same record. Is there away for a layout to omit records automatically without using script triggers?

[doughemi]

Let's back up and discuss WHAT you need to accomplish, instead of HOW to accomplish it. Once we know that, perhaps we can improve your data model.

 

What is the process you wish to automate, described in terms of the business purpose rather than the FM steps?

[qube99]

You might set a global variable containing the record ID while you are on you record of interest. When you get ready to go back use that global variable which contains your record ID. There's a couple of other ways to approach this.

[LaRetta]

Let's back up and discuss WHAT you need to accomplish, instead of HOW to accomplish it. 

 

I agree.

 

Hi James,

 

What you have been describing does not feel 'normal process' to seasoned developers so either we are missing understanding an important aspect or you are taking a wrong turn here.  We will not know which until we have specifics of your situation.

 

But no, there is no way for a layout to omit records by itself.  You need to perform a find first or use a GTRR or some other technique.  But until we understand what you are trying to accomplish, we can't offer solution.  Hang in there ... we understand that communicating only with words is difficult and we'll work through it with you.

[James Patrick]

Hi,

 

Thanks for the replies. Doughemi you are right I think I could be over complicating this, I would definitely describe myself as a novice. I have attached my in progress database and the layouts I am having problems with are Jobs and Enquiries. They are both based on the Job header table. When an enquiry comes in you generate a new number with a dept code with a Q in front of it (QA,QE,QP etc) When it becomes a job the Q disappears and you are left with the dept No. (A,E,P etc). To start with I had the enquiries on the jobs layout on a separate tab then all you had to do, when the enquiry became a job, was change the code in the dept dropdown.

 

The reason I separated it onto another layout is because the list view is very important and it was getting cluttered up with both the jobs and enquiries. I basically wanted to look at the jobs list view and see all the live jobs or click on the enquiries list view and see all of the enquiries and then have filters on both views to see the different departments.

 

Please see the attached file any help on this would be greatly appreciated as I am stuck and am not sure of the best way forward.

 

James

 

Thanks LaRetta. I appreciate your patience, the help I have received on this forum has been incredible.

 

James

In Prog.zip

[doughemi]

Thanks for the sample file.  However, you still haven't described the work flow.  Here is how I THINK the solution should be set up, but I can't be sure because I don't understand the objective yet.

 

Enquiries and Orders should be in one table, Jobs.  A radio button field (orderStatus) would determine whether the job is an enquiry or an order. 

 

You need a table for jobs, one for departments, and a join tables (tasks) between the two. One Table Occurrence of jobs (jobs_list) should be used for the list. There should be a global field (k.orderStatus) to allow you to show enquiries or orders. It should be related to another TO of jobs (jobs_detail) by

 

jobs_list::ID = jobs_detail::ID  and

jobs_list::k.orderStatus = jobs_detail::orderStatus

 

A button on a list record should trigger a script to Go to Related Record using the details layout. An if statement in the script would determine whether to go to an enquiry details layout or an order details layout.  If you want to go back to the list, another script with a Go to Related Record script step will do it. 

 

If you want to filter the list by departments, another global field k.departments would be added to a relationship.

 

Each record in the tasks table will be one task by one department for one job.  There may be different types of tasks for an enquiry vs an order, so the fields in the tasks table would be

 

jobID

departmentID

jobStatus

task description

<other fields related to that task (e.g. estimated hours, actual hours, scheduled completion date, actual completion date, etc)>.

 

Of course, any job would have multiple tasks, with multiple departments.  This is the reason for a join table, to define relationships between many of one thing and many of another.

 

Relate this table to jobs by jobID and jobStatus, and to departments by departmentID.

 

This may be expanded with relationships to a materials table, perhaps.

 

 

Other TOs of tasks can be used to populate portals or related layouts to show all tasks for a job, or all tasks for a department, or all tasks for one job in a department --whatever you want to track. Navigation to the correct layout for the objective would again be with scripts or buttons employing GTRR steps.

 

Post back if you need more details about a specific portion of this schema.

 

If I have totally missed the mark, then please define the work flow (Person A needs to do this, then that, then the other; person B needs to see something and another thing, and then add this detail) more completely.

[James Patrick]

Hi Doughemi,

 

Thanks for taking the time to try an work out a solution to what I am trying to do! I will try and explain the work flow a bit better:

 

• When a new enquiry comes in a person opens the enquiry layout and fills out the form.
• An enquiry number is generated and a dept code is picked from a dropdown list. Each enquiry is allocated to one dept. (On the enquiry layout the dept code begins with a Q, this helps us track the hrs dedicated to enquiries and quoting)
• Once the enquiry form is filled in you can press a button which sets various fields then goes to the contacts layout and fills in the relevant fields and allows you to add extra information.
• If the enquiry becomes a job you need to give it a new dept code which will be the same letter minus the Q
• On the jobs list view you need to have the ability to see all the live jobs or all the live enquiries but not both together.
• On the jobs list view you need to have the ability to filter jobs by dept code.

I hope this makes it a bit clearer if not don't hesitate to ask. 

 

On the jobs layout I have a job status field that would allow me to differentiate between jobs and enquiries and would make the script simpler than basing it on the dept code.

You mentioned that I need a table for departments I am not sure that I understand this as we only have one dept per job.

You have certainly given me a lot to think about and I am sure that I will have lots more questions as I play around with the solution.

 

Thanks again

 

James