Script Help required

[James Patrick]

Hello, any help with this would be greatly appreciated as I am really stuck with this problem.

 

Basically I have a table for Jobs and a table for Purchase Orders(POs).

 

One job can have many POs.

 

I have a field on Jobs called archive and a field on POs called archive which is a check box. When a Job is finished it is archived to remove it from a jobs list and the same goes for the POs. 

 

What I would like to do is be able to archive a job and that in turn archives all of the related POs.

 

I think the approach would be to set a script trigger so that when the checkbox is crossed it goes to the related records and goes to the checkbox objects and crosses them. This is as far as I have got. 

 

Any thoughts as to if this is the best approach would be a real help.

 

Thanks

 

James

[Ocean West]

if you start on jobs and then archive the job then you GTRR to the PO's either REPLACE the checkbox field with a boolean 1 or loop thru the records and set the field with a 1 to archive the individual PO record. 

 

In either case you need to trap for errors so that if someone has a record open if it fails the entire procedure and will not leave the locked (opened) PO record un archived. A bit more defensive programming. Especially if you have a lot of users, in the system. 

 

Another option is to create a new record in a "task" table that records the unique ID for each record in the jobs table and in the PO table. Then have server perform a script after hours to go to this table and loop thru and set the Archive field in their respected related table then delete the task. 

[comment]

One job can have many POs.

 

I have a field on Jobs called archive and a field on POs called archive which is a check box. When a Job is finished it is archived to remove it from a jobs list and the same goes for the POs. 

 

Is there a scenario in which you would want a Job to be archived, while one or more of its POs should remain active? Or the other way round: archive some or all of a Job's POs, but leave the parent Job itself active?

[James Patrick]

Hello,

Thanks for the replies. I was hoping that there would be a simple solution as I am fairly new to FileMaker.

With regards to the scenarios Comment mentions, no there isn't an instance where you might want Po active or vice versa. Basically when you archive it just stops the jobs showing in the active jobs list and POs showing in the active POs list.

I do have a portal on the jobs table that shows related POs, is it possible to check a box next to each PO in the portal if an archive checkbox is crossed on the jobs table?

Thanks

James

[comment]

With regards to the scenarios Comment mentions, no there isn't an instance where you might want Po active or vice versa.

 

Well, then there is a very simple solution: remove the Archived field from the POs table, and determine the archived status of a PO by looking at the archived status of its parent record in Jobs.

 

This is not only the simplest solution, it's also the correct one in terms of keeping your data normalized.

[James Patrick]

This sounds great Comment.

In the active POs list view I currently have a script to omit records with the archive checkbox crossed. This script is triggered on layout enter, is it possible to give me an idea what script I could use to do this?

Thanks

James

[comment]

 I currently have a script to omit records with the archive checkbox crossed. This script is triggered on layout enter, is it possible to give me an idea what script I could use to do this?

 

The same script, except that instead of searching the POs::Archived field, search the Jobs::Archived field.

If you can't make this work, post a printout of your existing script.

[James Patrick]

Hello Comment,

 

This worked absolutely brilliantly and gave me the simple approach I was looking for.

 

One issue has arisen which is causing me problems that I hope again you will have a simple solution. Basically for POs we use form view and list view. When you do a find in form view it shows the found set but you might want to view the found set in list view. The problem is as soon as you go to list view it triggers the script and changes the found set.

 

Is there away of not triggering the script if in a found set?

 

Thanks

 

James

[comment]

The problem is as soon as you go to list view it triggers the script and changes the found set.

 

Uhm, what exactly does your script do? If it omits the archived records from the current found set (using Constrain Found Set, rather than Perform Find), then the only time it would change the found set would be if you had some archived records in it.

 

If that's not acceptable (i.e. there are times when you need to switch to this layout with a found set that includes archived records and not have it changed), then you're really asking how to bypass the trigger in some circumstances. Which in turn begs the question: what are those circumstances? IOW, how would Filemaker know if you want the found set to be pruned or not?

[James Patrick]

Hi Comment,

 

Basically I have a script which says perform find/omit records on the job sheet that has the archive box crossed, based on the relationship between jobs and POs.

 

Everything works fine but sometimes people do a PO search on form view and when the found set is quite big they switch to list view and trigger the script. Strangely I thought is would behave as you said and just show the records in the found set that are not archived but it doesn't seem to do that.

Should I use constrain found set and not perform find?

 

Ideally I would like to bypass the trigger when showing a found set so that you will be able to do a search in form view and switch to list view and not trigger the script.

 

Thanks for your help with this.

 

James

[comment]

Strangely I thought is would behave as you said and just show the records in the found set that are not archived but it doesn't seem to do that.

Should I use constrain found set and not perform find?

 

Yes. Perform Find does a brand new find, starting from all records in the table.  Constrain Found Set searches only the current found set (as you would expect from the name).