Need help in writing a script

[pmkprog]

I need help in writing a script that compares the value of a global field and checks all records in the table to see if any of them have that value. If the value exists, then a dialog will cancel creating a new record in the table. If the value does not exist, then it will perform two set field functions and then end.

Can anyone offer suggestions on the checking value portion?

[Fenton]

It depends entirely on what you mean by "have" that value. If you mean, "must be exactly that value," then a relationship between the global and regular field will be fastest and best. Just check if the value is empty via the relationship.

Exit Record

If [ IsEmpty (Global Relationship::Field) ]

[pmkprog]

Thank you.

My Box ID number is the relationship and it must be exact.

They else option I require is to edit the box.

[Fenton]

Then a self-relationship from the global to the regular field is the way to go. You don't have to disturb the current found set (other than adding a new record to it when there's no match) to use it.

An addition to my earlier script would be to check that the global has a value before you start that script. Otherwise the relationship test will always fail and create a new record. So the If line would be:

If [ not IsEmpty(Global) and IsEmpty (Global Relationship::Field) ]

[pmkprog]

Do you have any suggestions on writing a script that will change a "status" if the job was invoiced in a previous month?

I'm looking for something that would work based on the "today" calculation.

Again, I'm new to scripting and it's not what comes naturally, so I very much appreciate the help.

[Fenton]

Well, yeah. But this is an entirely different question and operation from the earlier ones; which were concerned with either creating or not creating a new record.

It's easy enough to write a script that will compare a date to today's date. The bigger question is how and when to run it.

There is no reasonable way to write a date-sensitive calculation to automatically change a status field; because all such comparisons need to be Unstored to be accurate; but making them unstored makes them slow. They are generally heavily used for Finds, so this isn't desirable.

So, to get a fast stored date-sensitive status you need to run a script that processes that record(s).

There are a couple ways to do this. One is to create a stored calculation field of the Year and Month, result number:

_cYearMonth = Year(DateInvoice) * 12 + Month(DateInvoice)

Actually you don't really _need_ the calculation; you could do it with 2 If's, first on the year, second on the month. But that would be slower in a Loop on many records; which is likely what you'd want. You would also want to first Omit records that have already been paid, with a Find I imagine.

Freeze Window

View as Form

Go To Record/Request ["Last"]

Loop

[pmkprog]

I need to change the status to sort out what jobs have been billed in that particular month.

I'm sorting them in a separate portal through that status. I want the script to not only compare the month, but also take in consideration of the year. I'm hoping there's an easy way to compare both and return a chnage in the status value so that the portal won't display the files that fall out of the current month and year.

Does that make sense?

[-Queue-]

Hey Fenton, just a clarification. Should the Go to Record [ Exit after last, Previous ] step be outside the If? It seems like it should be the Else of the If, or it skips records.

[Fenton]

Oops. I should never write text Loop scripts.

Freeze Window

View as Form

Go To Record/Request ["Last"]

Loop

[pmkprog]

Well, I ended up using the calculatino formula to get the greater than condition and that seems to work just fine with a loop that checks through the records when the file opens. As the file gets larger or we migrate to fm7 I will probably need to change it.

Again, thanks for all of the help the lsat couple of days.

I joked with my supervisor and said he should send a check.

[Fenton]

We never ask for money here, but we don't refuse it either :-/

As I mentioned earlier, you really should do a Find with Omit to remove "paid" invoices from the list before the loop. That way it will be a far smaller number of records, and the number will not get all that much larger.

I don't think it would be much different in 7, other than the name of "Status(CurrentDate)", which would be converted to "Get ( CurrentDate )"

And you'd want to be on a layout for this table when it ran. Opening scripts behave a little differently also.

[Gill Ent - the best a man can get]

Can anybody help?

I have a CSV file of English towns. How would i write a script in Filemaker Pro 6 to sequentially check if each town is in a particular field of my database, and, if it is, to check a radio button "England" for that record?

Any advice greatly appreciated.

[Oldfogey]

Is that a one-off problem? If so, forget the script. Convert your CSV file to a FMP DB. Then create a relationship from your DB to Towns based on Town.

DB_Town::exCSVTown

Country = Case(DB_Town::exCSVTown, "England", "")

If you cannot define Country as a calc field then use a Replace with the same calculation.

If you really must use a script, then create the file as above and just loop through your DB and do a

Set Field (Country, Case(DB_Town::exCSVTown, "England", ""))

on each record.