Noob. Heeeeeeeelp. pls.

[stefanshotton]

Im fairly new to filemaker have been using alpha5, im stuck!

I need to perform a find against numerous fields, the type im getting stuck with is the date fields.

Im looking to perform a scripted find on a sales related table.

Include Text Field ="T" (ok)

Include Date Field (date Invoiced)*(those with dates entered, works ok)

Exclude Date Field2 (this is the bit im stuck with, help suggests just putting in an equals sign and thats it to say the field is blank, does not work though.)

Also want to get a total of the sales value(numerical field) within this find, have tried using a summary field to calculate but again its spitting our ant unrelated number.

Thanks in advance if anyone can help.

Thanks in advance

[-Queue-]

You mean like

Enter Find Mode [ ]

Set Field [rel::textfield; "T"]

Set Field [rel::datefield2; "="]

New Record/Request

Set Field [rel::datefield1; "="]

Omit Record

Perform Find [ ]

meaning that you want to find records where the related datefield1 has a value and datefield2 is empty?

Summary fields are for local found sets. Use a Sum(relationship::field) calculation for related summaries. In this case, you'll need to get the serial for all found records into a global text field, create a relationship from the global to the related serial field, and use that in place of 'relationship' in the calculation's definition. The quickest way to grab all the serials is to create a layout containing only the serial, then using a script to go to that layout, Copy All Records, go to a layout with the global, and Paste.

[stefanshotton]

Thanks, got the first formulas off to a tee now, hadnt tried using the set field in a script, works a charm.

The global fileds bit has me a little confused, lets see if i got this straight.

i add to the script just created a copy records script (using the found records only?) to a copy set, then use a global script to perform the total?

[-Queue-]

Almost. First you go to a layout that has only your serial (unique id) field. When you Copy All Records on that layout, all serials for the found set are copied as a list. When you go to another layout holding the global and Paste into that global, the result is a carriage-return delimited list of found serials. Using a relationship from this global field to the parent key field in your sales table (the field that determines whether the current record is related to any of the records in the other table) you can create a calculation field to Sum all related values for a particular field based on only the records in the found set.

Basically, if your list of serials in the global ends up being

0001

0005

0007

then every record in the found set will be related to records in the other table with a parent key field equal to 0001, 0005, or 0007. This works because globals are the same value for every record in a table and because FileMaker recognizes multi-keys (return-separated list in a key field).

If your related number field is Amount, then a calculation field of Sum(relationship::Amount) will give you the total of all Amount fields in relationship's related records (those having 0001, 0005, or 0007 as their parent key value).

[stefanshotton]

Im really struggling with getting this to work,

ive set up a new table containing two fields

field 1 is the customer id

field two is the calcualtion

ive put a relationship from customer id to customer id contained in another table.

I run the find script.

Copy Records

Paste into the customer id field in new table

WHAT NEXT???

[-Queue-]

You have to go to a layout containing only the customer id before you Copy ALL Records (not just Copy Records). Then you go to a layout holding the global and Paste into it, not into the customer id field. The global should be related to the child table and matched with the parent key in the child.

You don't need a new table. It should all be done within the parent table.

You can zip and attach your file if you need additional assistance.

[stefanshotton]

Can I email to you, the database holds client data that i wouldnt want on the world wide web :-)

[stefanshotton]

Thanks JT, have tried to email you but your address is not working.

[-Queue-]

Did you zip the file or send it as an fp7?

[stefanshotton]

Neither was plain text message