Search by Year in all tables

[pfarrell]

hi FM pros!

i've tried to work through this on my own with other tips i've found on this (and other) forums to no avail ... please help.

i'm in the process of reworking my db which i use for my biz. it contains clients, projects, expenses, invoices, etc. in one db (multiple tables and layouts of course). at the moment all i need by way of searching is to be able to view all records in all tables that have a date field by year (no days or months) ... i only need to view records by year in any given table at any one time ... but if i switch to a different table (that has a date field) i want the "find by year" script to auto update the sort to reflect the year input i may have previously set in another table.

i figured this would at least require a global field (Year_Global) into which i'd enter the year i want to sort by (this field would be on any tables that i'd want to search/group by year)... and i thought it would require a "go" button to activate the search (script) ... the tough part is that it seems necessary to separate the date info in the date fields which all include a full date (xx/xx/xxxx) ...

i've also had some issues with found scripts not working maybe because of FM version changes ....

i'm working with v7.

any help would be much appreciated.

thanks!

[RalphL]

Add a calculated number field Year = Year(Date). Use this in your finds. Use the global year in your finds.

[Lee Smith]

Hi pfarrell, and welcome to the Forums.

Ralph has provided one way.

I have attached a simple file showing how you could view these by a Portal too.

HTH

Lee

RelationshipDateYear7.zip

[pfarrell]

Wow.

thanks for the quick response and the attachments. maybe my question came across as sounding more advanced in terms of how to actually get it done than i actually am ... i can visualize exactly what i want to do .. it's doing it in FM that's a bit non-intuitive (for me) ...

i'm looking to not use the FM find mode (unless it's entered via a script and i'm not aware of it as the user) ...

-Ralph, i'm not exactly sure how i would apply that to my sittuation ... i'm definitely in the "newbie" camp when it comes to FM ... hardcore XHTML and CSS no prob. ... guess i need things a bit more spelled out.

-Lee, the portal version you sent me works exactly how i want it to except for the portal ... the other

file confuses me a bit ... is it possible to enter the year (in a field in the header) as in the portal version but have all records with matching years (as part of their complete dates) show up in list view below the header? that's how i have it set up now ... without the year search working of course ...

thanks again for the quick responses,

patrick

[Lee Smith]

You mean like this?

HTH

Lee

Contact_AIO.fp7.zip

[pfarrell]

hi lee,

file attached with a version of what i'm trying to do ... enter a year, hit "GO"

(if needed?), and the list sorts to show only the year specified ... i would set

the script that does this to run on opening the db so it would show the current

year ...

your first portal version was spot on ... except i want to do this w/o using

portals. i have mulitple tables and i want to enter the year and have all records

from that year show up ... if i switch tables, the "year" field is global and the

year set will apply to all tables that have a date field and the global "year" field ...

basically i want to exclude all records for all years except the current one in

all tables and layouts throughout my db ... but if i need to reference all records

from a prior year ... i just enter a new year once ...

thanks for the help!

patrick

Year_Search.fp7.zip

[-Queue-]

See attached for modified version of your file. If you want this to be applied to all appropriate layouts, you will have to activate a similar GTRR script when you switch to another layout.

Year_Search.zip

[pfarrell]

hey thanks that works nicely. it was more complicated than i thought it was going to be ... i don't fully understand the relationship between the table "occurrences" that are necessary to make it work ... does this have to do with the calculation of the traditional date format to just the year?

anyway. thanks again.

p

[-Queue-]

Yes, the global is related to the calculated Year field for the date. The main step in the script is the GTRR one. The others are just there to ensure that there is a value in the global field and that it does have at least one related record. If one of the tests fails, then there is no need to perform the GTRR step because it will not produce any records.