Enter Find Mode Script step Problem

[Jessica_BLUE]

Hello, i hope anyone could help, im new at filemaker so i dont know if im doing this correctly.

so, i got customers-->jobs-->jobxactions-->bills.

i want to do a report, based on jobs, showing the records of jobxactions and bills, currently the relationships between tables are correct, my problem is, when i try to do a search by dates, (i need the bills and jobxactions fields results) the find mode, omits my search criteria (jobxactions--datesorted) and shows me everything it founds in jobs table, how can i force them to find the dates in jobxactions table, although the report is based on jobs table.?? Please help... thanks.

[Søren Dyhr]

currently the relationships between tables are correct

Then explain why they are? Are you really issuing several invoices on each jobxaction?

Generally should the reporting be done in the most atomic of the tables, typically the jointable!

--sd

[Jessica_BLUE]

thanks for your fast response.

my relationships are: i got jobs and his respective jobxactions(hours worked on the jobs), in an specific range of time, but not always gonna have bills(there are the expensives for the jobs). i change my report now based on Jobxactions table, my problem is the same, when i do a search, i search in jobaxtions table, what i want is in case there are bills in the same time range, also brings them, how can i do this? currently brings me all it founds in bills table, no matter the date i set up, i'm not sure if im doing this correctly. appreciate your help.

[Søren Dyhr]

My point is that your relation could seem a little misleading, it's could seem like jobs that have invoices ...or do bills not necessarily follow the jobs? Is it so that Jobxactions in reality is the jointable between jobs and bill?

How.... or what method have been used to establish which tables were necessary to convey the desired effect, honestly do I think something has gone wrong here.

--sd

[comment]

If your report needs to include jobxactions that do not have bills in the reported period, then the report must be based on the jobxactions table. To "see" the child bills in the reported period, you will need two global fields (start and end) in the jobxactions table. Use these fields to build a relationship to another occurence of the bills table.

[Jessica_BLUE]

im not sure if i understand your question, i got jobs and for every job i got "x" number of jobxactions and bills, for every job i always gonna have jobxactions, but maybe in an specific month i could have or not bills (expensives), what i need is in case i have bills, i can show them by an specific date, currently brings me all the records it found in bills table. this is what i have:

thanks.

date_found_problem.zip

[Jessica_BLUE]

ok, so i have my report based on jobxactions, like you said, i got 2 global fields: BeginDate and EndDate in a TempVars Table, and by this i make my search in JobXactions::DateSorted, but how can i do the search in Bills::BillDate

enter find mode

set field[Jobxactions::DateSorted;Getastext(TempVars::BeginDate)&"..."&GetasText(TempVars::EndDate)]

Perform Find.

if i also put

set field[bills3::BillDate;Getastext(TempVars::BeginDate....etc]

in the case i dont have bills, obviously dont find me anything, so, how can i do to bring all the jobxactions records and if there are any bill records also show them in my report?

thanks for interest in my problem.

[Søren Dyhr]

im not sure if i understand your question

And I'm not quite sure if I ever would understand your relations - even if I tried, you seems to have tied everything together ... just because it's possible. But just because you can make the connections isn't necessarily a normalized solution.

It's about time you make TOGs into something pretty anchor bouy'ish before we even can digest it, and explain your choice of tables ...the RG isn't by the way an ERD! The next thing is an explanation why you think your relations graph gives you anything usefull.

My initial thought when looking at something one2many2many2many etc that here ought a recursive structure solve the matter better ... if it really was the case, but oftentimes is it just because the relations are thrown in because of inconvenient made choices of tables.

Comment's solution to your problem, might work, but as such is it very likely to be a remedy instead.

--sd

[comment]

I cannot read anything meaningful from your attachment.

There is a fundamental issue here that you need to understand before you can make any progress: finding records in the bills table is completely irrelevant, if the report is based on the jobxactions table. This is why you need a relationship between jobxactions and bills - so that you can filter the related bill records by date, and include them in the report when they are related, whether found or not.

In your report, you would need to show the related bills in a portal. And, as I said, the globals need to be in the jobxactions table, or they cannot be used when defining this relationship.

Søren's idea of moving the bills into the jobxactions table, and linking them to their 'parent' jobxaction by a self-join is something well worth considering.

[Jessica_BLUE]

ok, i have the relationship between the 2 tables by JobId, i try to understand what are trying to tell me. Jobs is my Parents table, and Jobxactions and bills are the childs table. putting the relationships apart. what i need to do is create 2 global fields (begin and end) in jobxactions and with this make the relationship to bills? but how can i found the bill records?, sorry the mess, but i dont have much experience in filemaker. when i run the report, like i said, it brings me all the records it found in bills, what i need to do to tell filemaker that bring me, only the bill records, from specific dates, no matter if there are records? thanks for your help.

[comment]

Some things do not make sense here:

Jobs is my Parents table, and Jobxactions and bills are the childs table.

That's not what you said before. You indicated bills was a child of Jobxactions.

when i run the report, like i said, it brings me all the records it found in bills

Not possible, if your report is based on Jobxactions.

how can i found the bill records?

As I said, finding them is irrelevant - if your report is based on Jobxactions.

[Søren Dyhr]

sorry the mess, but i dont have much experience in filemaker

Fair enough! This could explain the bringing in of another realm as such - if it's whats goin on here? But there might be more to it than that. The attempted normalization still seems pretty eclectically performed ...there is nothing wrong if you intuitively can see the way things should go if it's correct, but when it as here, goes wrong ... well my present favorite quote says it pretty well:

http://www.fmforums.com/forum/showpost.php?post/281483/

So can't you reveal what kind of inner voice you abide to here? It could be that you have been drilled with circumvents unfortunately only applying to one specific other tool. Which not necessarily translate at all to filemaker, which on the other hand of course possesses it's own set of circumvents too.

--sd

[Jessica_BLUE]

maybe i express myself in a wrong way. i have the report based on Jobxactions, when i run the report i want all the jobxactions and bills records in a given date, for example: 01/01/08 -01/20/08.

so, i found all the jobxactions records from this date, right? but also i want the records from bills, my problem is that maybe, in that date range, there's no bill records or maybe they are, so if i perform a find also by bills, if there isn't any records, gives me an error message,(didn't find anything) because im telling filemaker: look into jobxactions from '01/01/08...01/20/08' [color:red]AND bills from '01/01/08...01/20/08'. what i dont know how to do it is a kind of [color:red]OR in the searching. my search is not irrelevant. so, in case, there's no bill records, either way found me the Jobxactions records, and if there are bill records find them, in the given range date.

[Jessica_BLUE]

yes, im aware the relationships are not the way they suppose to be, and that in a way have been working fine, until now. my problem is that i have a dead line for this report, and obviously i haven't find the way to make it work. could you please help me? :)

[comment]

I am still waiting for you to clarify whether bills are related to jobxactions, or directly to jobs.

The only thing that is clear is that a find performed in the bills table has absolutely no impact on a report produced from the jobxactions table. I think this is the third time I am saying this. If you don't want to accept this, there's no point in going on.

[Jessica_BLUE]

I have Jobs Table, for every Job (unique) i could have "x" number of Jobxactions and "x" number of bills records. the relationship are Jobs (one to many) Jobxactions, and Jobs (one to many) Bills.

i want to know by job, the hours i worked on (Jobxactions) and the expensives i generate (bills), thats why i need to also search in bills table, the reason i did my report based on Jobxactions table, was because, it seems easy to me to find the records, but my problem still radicate that i dont know how to show the bill records.

[comment]

OK, that changes the picture considerably. Let me say first that this is not an easy problem. I believe your best option is to combine both jobxactions and bills into a single table. If not, the simplest solution (although not the best one) is to produce the report from the Jobs table, with 2 sliding portals to the two child tables, filtered by the two global fields (which need to be in the Jobs table for this).

See also:

http://www.fmforums.com/forum/showtopic.php?tid/192139/post/276220/#276220

See these threads for some more advanced methods:

http://www.fmforums.com/forum/showtopic.php?tid/191147/post/271539/#271539

http://www.fmforums.com/forum/showtopic.php?tid/173299/post/191073/#191073

http://www.fmforums.com/forum/showtopic.php?tid/181674/post/227384/#227384

[Jessica_BLUE]

Thanks Comment, i appreciate the help, i can't join both tables (jobxactions and bills) but what i did is a portal with the records, inside the report and also a script which copies the records from bills to a Temporal Table, so only show me the records from the date range i want.

Thanks Again :)