a question finding data when it come from portal

Hello,

i was wondering someone can help me out with this. i am running FM 6.0
and i have this database call students and the second one call
Ledgers. In the student DB i have portal with the following fields:
Posted Date, charges, amount, category. The data for this field get
sent to the ledgers database. What i need to figure out when something
got posted to the field Amount base on the Posted Date. is this
posible?

thanks
Vic

Re: a question finding data when it come from portal

In article
<642219a4-509d-4f07-938c-43af6d9818a9@v29g2000hsf.googlegroups.com>,
WebMaster wrote:

> Hello,
>
> i was wondering someone can help me out with this. i am running FM 6.0
> and i have this database call students and the second one call
> Ledgers. In the student DB i have portal with the following fields:
> Posted Date, charges, amount, category. The data for this field get
> sent to the ledgers database. What i need to figure out when something
> got posted to the field Amount base on the Posted Date. is this
> posible?

If I understand correctly, then you should be performing the Find in
the Ledgers database, rather than via the portal.

If you perform a Find using Portal fields, then FileMaker returns the
Student records that have ANY Related records that match the request.
Those Student records may also have other records in the portal that do
not match.

If you perform the Find in the related Ledgers database itself,
FileMaker will return only the matching records.

You may need to create a new Relationship from the Ledgers database
back to the Students database (using the same key fields as the one
from Students to Ledgers) to be able to use Student's fields, eg. Name,
in any displayed or printed reports.

Helpful Harry
Hopefully helping harassed humans happily handle handiwork hardships ;o)

Re: a question finding data when it come from portal

On Jan 10, 11:05=A0pm, Helpful Harry
wrote:
> In article
> <642219a4-509d-4f07-938c-43af6d981...@v29g2000hsf.googlegroups.com>,
>
> WebMaster wrote:
> > Hello,
>
> > i was wondering someone can help me out with this. i am running FM 6.0
> > and i have this database call students and the second one call
> > Ledgers. In the student DB i have portal with the following fields:
> > Posted Date, charges, amount, category. The data for this field get
> > sent to the ledgers database. What i need to figure out when something
> > got posted to the field Amount base on the Posted Date. is this
> > posible?
>
> If I understand correctly, then you should be performing the Find in
> the Ledgers database, rather than via the portal.
>
> If you perform a Find using Portal fields, then FileMaker returns the
> Student records that have ANY Related records that match the request.
> Those Student records may also have other records in the portal that do
> not match.
>
> If you perform the Find in the related Ledgers database itself,
> FileMaker will return only the matching records.
>
> You may need to create a new Relationship from the Ledgers database
> back to the Students database (using the same key fields as the one
> from Students to Ledgers) to be able to use Student's fields, eg. Name,
> in any displayed or printed reports.
>
> Helpful Harry =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0
> Hopefully helping harassed humans happily handle handiwork hardships =A0;o=
)

Harry,

thank you for the help. what i am trying to do is to find out is how
many have past since a student made a payment. i think the reason i
cannot get it to work is because i am using the posted date for
charges and payments. i need to figure out a way to break it apart.

thanks
vic

Re: a question finding data when it come from portal

In article
<393987c0-eea5-46d7-a4f5-6a7be61e71b0@i29g2000prf.googlegroups.com>,
WebMaster wrote:

> Harry,
>
> thank you for the help. what i am trying to do is to find out is how
> many have past since a student made a payment. i think the reason i
> cannot get it to work is because i am using the posted date for
> charges and payments. i need to figure out a way to break it apart.


Sorry, I misunderstood what you were trying to do.

You will need to split Payments and Charges in some way. There must
already be some way to know which Ledger records are payments and which
are charges - for example, one might use negative numbers while the
other is positive. Once you know how to tell them apart, you can create
a new Calculation field to automatically create a difference.
eg.
SeparationLink Calculation, Text Result
= If (IsEmpty(StudentID) and IsEmpty(Amount), "",
StudentID & If(Amount < 0, " - CHARGE", " - PAYMENT")
)

Note: I'm assuming StudentID is the field that you are using for the
current Relationship definition to link Student records with their
Ledger records.

Click on the OK buttons to get out of the Define Fields window and let
it calculate this for all the records. This field will be used as the
child-side link field for two new relationships, so it can not be left
as a Calculation - instead go back in and change the field to a normal
Text field (FileMaker will retain the calculated results), but give it
an Auto-enter by Calculation option using the same forumla as above so
that future new records are given the correct values.

Back in the Students database you can then create two new Calculation
fields:

PaymentsLink Calculation, Text Result
= StudentID & " - PAYMENT"

ChargesLink Calculation, Text Result
= StudentID & " - CHARGE"

These can be used as the basis for two new Relationships from the
Students database to the Ledger database (keep the original
Relationship as well). Each one would be based on a Calculation field
in the Students database:

rel_Payments PaymentsLink = Ledger::SeparationLink

rel_Charges ChargessLink = Ledger::SeparationLink

This gives each Student record a link to just their Payment records AND
just their Charges records, while your original Relationship retains
the link to the combined Ledger records.

From there you can easily get the number of days (for example) since
the last payment with a Calculation field in the Student database can
work out the newest payment date using the Max function and subtracts
that from today's date.
eg.
NumDaysSinceLastPayment Calculation, Number Result, Unstored
= Get(CurrentDate) - Max(rel_Payments::Posted Date)



Helpful Harry
Hopefully helping harassed humans happily handle handiwork hardships ;o)