Hi,

Is it possible to call a script from a calculation field?

This is what I am trying to achieve:

I have a dropdown field, and I need a script to run whenever something is selected in this field.

My code is simple; I am using several IF and EndIfs.

Set Field [Total_Paper;0]

If [Left(Items[1];5) = "Paper"]

Set Field[Total_Paper; Total_Paper + Line_Total[1]]

End If

If [Left(Items[2];5) = "Paper"]

Set Field[Total_Paper; Total_Paper + Line_Total[2]]

End If

Since the above is not possible in a calculation field, I coded the above in a script

Any help is sincerely appreciated.

Thanks.

It is possible only with plug ins that I know of. The plug in is free but a little complicated to get working.

I can't remember the name of it for the life of me.

You might try googling it.

Thank you very much. Any idea if the plug-in has to be installed on each client computer that has FM Pro.

Yes. The plug in (I like zippScript, though there are others) would be installed on each client to work (but not the server).

I am not at all convinced this cannot be done with calculations only. Of course, I'd need to know what "this" is to be sure. I suspect the problem is at least partly caused by using repeating fields where a related table should be used.

It is quite possible that it could be done via calculation field. I would prefer to do it that way, because then I don't have to worry about installing the plug-in on all user's computers.

But I am not a pro in FileMaker, and am struggling to get this done in calculation field. This is my requirement:

I have a order entry system. Each order can have multiple items such as paper,ink, labor etc. It is also possible to have different kinds of paper ordered in one order, and each has a different rate. I need to generate one total for paper, one for labor etc. The user has four columns to populate the order info. - Quantity, Items, unit cost and price. Item is a dropdown list. Each of these columns are repeating upto 20 times. I would like the total to be generated as and when the user selects an item. However, because this is a dropdown box, I am not able to call the script.

The final outcome of these paper_total, labor_total fields are to be used in a report.

I'd appreciate if you could suggest a way to do this in a calculation field.

Thanks.

This can be much simpler than you think, but you need to make some structural changes (as I suspected). The order's line items must go into a separate table, with a record for each item. Use a portal to show and create line items directly on the order's layout.

You didn't say where the item values are kept - I hope you have an Items table for this? In this table, you assign a category to each item.

Your report can be easily produced from the LineItems table, with a sub-summary by Category.

Thanks. Unfortunately I do not have the luxury of making any structural changes at this point. The table is de-normalized. All the data is kept in one big table. Can something like this be done:

When the user goes to generate monthly report, trigger a script that finds all the records in that date range, loop through them and generate the totals, then proceed with the report layout.

Thanks.

This doesn't add up:

It is quite possible that it could be done via calculation field. I would prefer to do it that way, because then I don't have to worry about installing the plug-in on all user's computers

...with this:

I do not have the luxury of making any structural changes at this point

This means you have sufficient passwords even to throw it into a normalized solution, doesn't it?? Then do it ASAP!!!

--sd

Can something like this be done:

When the user goes to generate monthly report, trigger a script that finds all the records in that date range, loop through them and generate the totals, then proceed with the report layout.

Possibly, but you will be spending your time and effort on doing it the hard way, when it can be done the easy way. I do not have such luxury, so I am afraid I cannot help you with that.

BTW, you will very likely run against the same problem with other reports or features you might want to add in the future. You may discover elaborate workaround for those too - or not. I truly believe you'd be better off fixing the basic flaw once and for all, rather than constantly working around it.

Edited October 4, 200718 yr by Guest