Validate 2 field from another FM DB and then copy

[brettallan]

I have two FM databases Main and TimeSheet, in timesheet you enter a job number and a status code status (1 to 5) and the start and finish time on that job - it give you the hrs/mins used for that job - setup in a columnular page/line entries.

The main Database holds the job number for the look up and that works fine. What I want to try and do is run a script from the Main database that will open the timesheet database and use the following logic....

get currently browsed record jobno of main database, match to equivilent entries of jobnumber in timesheet database and also match a status code for example = 3, if bth these events are found to occur in the found records then copy that total time from a calculated time field and past back into a field in the main database where it can be attached to pricing and generate a bill.

eg Timesheet DB

job no 44444 code1 2hrs

job no 44444 code 1 1.15hr

job no 44444 code 3 2.45hr

job no 33333 code 1 1hr

current record being browsed in main db is 44444

script/button would then match job numbers up

and give follwing answer:

For job 4444

Code 1 Count = 2 Code 1 total = 3.15hrs

Code 2 Count = 0 Code 2 total - 0hrs

Code 3 Count = 1 Code 3 total = 2.45 Hrs

Code 4 Count = 0 Code 4 total = 0 Hrs

Can some one help... I've tried If and statements and finds through scripts and I can get it to add all occurences of job number but not code status or vice versa.... Hopefully one of you guru's might have an idea...

Thanks in advance!

[jasonwood]

This shouldn't be too difficult, although I don't understand everything you've explained (what is the "code" for?). I'll take a stab at it though...

Set up a relationship to TimeSheet using jobNumber as the key field. You can set up the relationship to sort by "Code".

Put a portal in Main.

In the portal you can put related fields for # of hours and the Code. (This will show the number of hours for every entry in time sheet, and it will tell you the code. The entries will be sorted by the code)

You can then put a calculation field in Main -- SUM(TimeSheet:# of hours) which will sum up the # of hours field in the related records.

Did you need the system to summarize the number of hours for each job "code"? I won't go into it in detail without knowing for sure you need this, but you can experiment with relationships using combined fields (eg: JobNumber & "-" & Code).

Note that the above system will not require any script.

[brettallan]

Jason - thank you!

Yes you are right... I will need to summarize total hours for each status code for that job no. - there are 9 (some chargeable codes and some not (eg. Briefing time (is non-chargeable, whereas creating is chargeable - these are two of the status codes) sorry for confusing the issue with "code"

so i would want to summarize totals against each status code for that record

the next step would be to add the chargeable summarized "status codes" times and bill those and total the summarized non-billable "status codes" and just keep this record for the file.

Is this possible...

PS thanks agin so far.... the firsdt bit you suggested works great!

[jasonwood]

Is the only difference between codes (for our purposes) whether they are chargeable or not? And is that the only reason you need to separate them?

If so, it would be easier to just add another calculation field to your TimeSheet, "chargeableHours"

The calculation would check if code is one of the chargeable codes, and if so, make it equal to the total number of hours. Otherwise, it would be zero.

Then at the end of the portal you could summarize both total hours and chargeable hours separately using the method I explained before.

Would this be sufficient? or do you really need to have totals for EACH code? How many codes are there?

[brettallan]

Jason

there are 9 status codes, the chargeables all have different raw costs per unit which will need to be summarized and passed through and calcualted at the main database? not sure where we would calcualte this?

The non-chargeables codes need to be summarized and total time recorded for each as well - management purposes.

Is this possible?

[jasonwood]

For each item in TimeSheet, you can have a calculation field "amountBilled" that checks the code and calculates the cost.

eg:

case(code=1, totalHours * 20.50, code=2, totalHours * 13.90, 0)

in this example, code 1 is billed at 20.50 per hour, and code 2 at 13.90 per hour. All other codes are zero.

then your "sum" field in the main database will simply sum up this calculated field to find the total amount to be billed.

[jasonwood]

...That works well for calculating the cost for each job... but if you need to summarize the hours/$ billed for each code (for management purposes, that's probably best to be done right in the TimeSheet database. You could run a report on just one job, or an entire range, through a find.

Try going through the step-by-step report generation process. It will ask about how you want things sorted, and summarized.

[brettallan]

so there is no way to summarize hours/billed$ for each code and forwrd the answer back to the main database? It would be nice to keep management in one database, without having to run reports from others and then manually add figures back to the main database.

no other ideas?

[jasonwood]

Well the only thing I can think of is to have separate relationships for each code, based on a new calculated field, which would combine the job number and the code field to create a narrower relationship.

Then you can summarize each code in its own portal.

Of course you could just script the whole process if you want, but you'd have to make sure the script runs whenever a change takes place, otherwise the information will be out of date.

[brettallan]

that's what I thought... back to a script... I cant change the existing databases....

sorry to be pain,,,, but any ideas on how this script would be performed, script steps.... I have made a few and they all fail at one spot or another....

Thanks for all your help so far... I've finally had a few good night sleeps at least with some of the ideas that have worked for me.

[jasonwood]

Scripts can only control databases other than the one they are in, except for activating external scripts and passing data through to related fields.

So... create a global field in one of your db's to serve as a temporary variable.

In your main database, you activate a script, which copies the job number to the global field, then runs an "external script" in the other db.

You'll probably set this external script (in TimeSheet) up to enter find mode, set the data from the global field (and maybe a bit more--eg: the code you are looking for), then do a find. Then do whatever operations you want, and sent the result back to the global field.

The original script would then continue by continuing to work with this data (or simply store it).

Since you are getting more than one number, you can either have multiple global fields (for the different results), and a longer script that performs more finds... or you can have multiple scripts in the timeSheet which can be run sequentially (one at a time) from the main database.

This is just a brief example, but I think it will give you an idea of how to work with external scripts and globals.

BTW, you need to have defined a relationship in order to get a global from another file, but the relationship need not be "true" in order to access the global field.