I want to have a field that show the last related record from a relationship:

The mysterious field [:] must contains [facture::no.facture] wich is the related field that containt serial number like F-128

I search but found not any kind of "show last related record" function

I don't think that the function that you describe does exist.

But what you might want to achieve has been described

here ,

here ,

and on numerous other places in this forum.

Regards,

Ernst

You can use the Last() function. The documentation only lists it as a function for repeating fields, but it works on related fields too:

Last(TheRelationship::TheField)

Hi Bob,

Did not know that.

Thank you!

Ernst.

Can't you sort the relationship in descending order?

.... with a portal of size 1

That was quick !!

But i discover that it's not what i want want exactly.

But it works great!!

Thanks

But can i do the same by confirming that the [facture::no.facture] is matching the date of my current record file

Then you'd need to change your relationship defs to a Concanate calc Invoice#&"-"&InvoiceDate...I think

Hi Jofftan,

I think we can put this together for you. But the guys need some clarificaton:

"The mysterious field [:] must contains [facture::no.facture] "

Are you saying that Facture is the name of your dB? Or a field? Or does the field contain the words 'facture'? Or are you saying that it is the last record because it's not empty or last in a series of serial numbers?

To isolate the last 'facture' record for each UniqueID, we need these answers. We also need to know the data type of 'facture.'

You can go ahead and create a self-join on your UniqueID. Once we have answers to the above questions, we can give you a calculation for your 'mysterious field.'

Hey Ugo! What's Invoice got to do with this? You been into the whacky tabacky or is your mind still on Jason's post?

Anyway, the calculation (data-type unknown at present) would be as Bob indicated, depending upon your answers to our questions.

LaRetta

La Retta, I just love Montreal so I don't reach my english limits...

Facture = Invoice...

Hey Jofftan, did you recognized the helmet.

Hi Ugo, It's good to know you weren't hallucinating. And I'm always happy to learn a new word.

LaRetta

Quick aparte La Retta,

What do you call when you recall a customer that owes money (from my last answer to Jason) ? English lessons live

Hmmmm, well I'd probably flunk this English lesson. But you know us Americans - we always do things a bit differently.

Worked in offices 35 years and haven't a clue what you're asking! Do you mean re-invoicing or re-facturing Or Outstanding Invoices? Everyone that owes a business money is known as Accounts Receivable or "Receivables"?

Nope, I don't know the answer.

Must be too many long FM hours lately!!

LaRetta

<< Are you saying that Facture is the name of your dB? Or a field? Or does the field contain the words 'facture'? Or are you saying that it is the last record because it's not empty or last in a series of serial numbers? >>

Facture(or invoice for it's translation) is the name of the DB. And last record is meant to get the last serial number.

To isolate the last 'facture' record for each UniqueID, we need these answers. We also need to know the data type of 'facture.'

It's a simple text data type

Hi Jofftan,

Okay, no self-join. You have a related file.

If these two dbs are currently joined on a FactureID (I neglected to ask how they were currently joined - mostly because I didn't know Facture was a db) and you have a field 'serial number' and want the last (largest number), you can use THAT existing relationship to get your calculation.

Last(Facture::SerialNumberFieldName)

If the existing relationship is based upon a FactureID, and you want to know the last FactureID in the whole facture db, you need a new relationship. Create a constant key calculation (number, index ON) = 1, join them, and create your calculation on that relationship as:

Last(NewRelationship::FactureID)

Last returns the same data type as the field. So if the 'serial number' field is text, the calc should be text. If the serial number is number, calc should be number, etc.

You can always help me out, guys!! We're still missing a piece here!

LaRetta

Oh, I see it! "must contains [facture::no.facture]"

no.facture is the name of your serial number field which is text!

Okay, use your existing relationship.

Create a calculation (text, unstored):

Last(Facture::no.facture)

LaRetta

Sorry everyone i'll rethink my db and will get back to you with a more precise explanation of the problems

Another piece missing to that puzzle...

But can i do the same by confirming that the [facture::no.facture] is matching the date of my current record file

:?

Case(FactureN

No Ugo, I'm the one that got lost.

I didn't see the 'Date' part at all.

I guess I'm beyond tired. Time to back off FM for a few days and recover my wits. I hope I didn't confuse Jofftan too much.

LaRetta

Hi LaRetta,

RE: I guess I'm beyond tired. Time to back off FM for a few days and recover my wits.

You have been posting alot lately. I also at times spend to much time working on Filemaker databases. Sometimes it is good to just take a break for a few days. It is the 4th of July weekend. Enjoy yourself!!

Michael

Hi Michael,

Hey, thanks for the words. I'm on a deadline (beta due Monday) and then ... well, I see these posts, and I get side-tracked!!! I have to read and understand everything that is posted.

24-7 causes brain-death and lousy design. You are absolutely right. I'll try to not think about multikeys or Value Lists or x-plat problems or anything for at least ... maybe a day!

LaRetta

I'll try one last shot

Facture db

with those fields

[delivery date]

[factureID]

Hosting db

with those fields

[active date]

[factureIDrel]

In Hosting db

Now i want the [factureIDrel] to show [facture::factureID] if [facture::delivery date] match [active date]

The 2 db are link in a relationship

Pretty schematic but i think it's more clear that way

Very good!! Have a great weekend.

Michael

Hi Jofftan,

I'm glad you came back and that you're trying again. Sorry for the confusion. I won't attempt to answer you though, wouldn't be good for you.

The guys will step you through this one!

All the best,

LaRetta

Thank you for your help and get rid of that deadline after all it's summertimes

What do you call when you recall a customer that owes money (from my last answer to Jason) ?

Ugo, maybe you are referring to a "statement" which is usually issued monthly, and contains a summary of all invoices and payments (and of course, interest charges)?

Maybe compte en souffrances (suffering account) ?!?!

Jofftan !

What on hell are you trying to say ?

Could you just summarize in "words" what you are attempting. I have no clue about what could your Hosting db do, nor what is about the delivery date.

Just put it like "I have a file for my deliveries, and a file for may invoices. They are linked from two fields, which are...

I want to be able to..."

Currently, it sounds like "Guess what I'm trying to do ! "

Bob,

You won this English Lesson...