Last available related record in a one to many relationship

[bluearrow]

Hi,

In a solution, I need a technique to identify when all related records have moved from step 1 to step 2.

There are 3 tables involved: Projects, Invoice, Lines.

The relationship from Invoices to Projects is one to many, so it is from Projects to lines.

So, an invoice is composed of one or more lines, of one or more projects. The process is all done from the invoice table:

- The user creates an invoice

- The user selects from a portal of availabe projects. Only available projects are shown.

- For each project the user selects, only the available lines are shown

- The user selects one or more lines from the selected project. This lines are added to the invoice and marked as not available.

My problem is that I need a way to identify when the user selects the last available line in the project. When this happens, the script should also mark the project as not available. I have not managed to find an easy way. I would like to avoid complex calculations at all. I am sure somebody from the forum has found himself/herself confronted with this issue.

Any ideas?

Thank you very much

[Søren Dyhr]

Make an extra TO and relate it with the opposite sortorder, first record will then be the last created...

--sd

[bluearrow]

Thank you Søren,

Just to make sure I understand your proposal: do you mean using that new TO for comparing the ID of the first record in that TO with the ID of the record being assigned by the user? Then, if both IDs are identical, then this record is the last record available.

Is that what you have in mind?

[Søren Dyhr]

Try to investigate this - hopefuly isn't the download on the blink yet?:

Ah! - It's still not working, So - dupe the TO for the portal, if necessary put a serial ID in the portal records and update the field with a Replace, if they aren't there. The new TO is the related as the old one but with a sort in descending order... set up in lower right corner of the relations def.

--sd

Edited December 8, 2007 by Guest

[Simon K]

Hi,

I have had a go at the situation,

See attached (though from Soren's note I hope you can download - if not I will upload again if necessary)

I think it does what you described...

There are some really clever guys and girls on here who have masses more experience than me so they may well have some insight that invalidates my approach (comments invited)

HTH

[Simon K]

Download still not working...

[Simon K]

Here is file again... down load seems OK now also see comments above

ProjLinesInv.zip

Edited December 10, 2007 by Guest

[bluearrow]

Thank you Simon,

I am having a look at your sample file. It looks great at first glance. I will get back to the forum once I understand its logic.

Thanks again for your sample file and for your effort to upload it even when the link was not working.

PD: Thank Søren, but I was unable to download your file. I do not know what is wrong with the link. Some links seem to be working, others not. Strange. Maybe the forum administrator could take a look... B)

Edited December 14, 2007 by Guest

[Simon K]

Hi Bluearrow,

Thanks for looking at the file, after posting it I had some further thoughts and have created a new topic to explore what might be "not the best practice in my demo file"

The issue appears to be whether you should script the creation (and subsequent editing of) the flag that marks a project as complete vs the management of the situation via relationships only.

(My demo is also slightly "flicky" which could well be a by product of the above)

The new topic is in Relationships forum under the heading "Scripted tagging - a big no-no???" and I think the link is at:

http://www.fmforums.com/forum/showtopic.php?tid/191906/post/275200/#275200

I am looking forward to improving my FM skills in what is obviously quite an important design area.

Simon

[bluearrow]

Try to investigate this - hopefuly isn't the download on the blink yet?

--sd

Søren,

In the light of the new thread, would it be too much to ask you to try to upload the file again? I think it might be useful to understand your reasoning in the new thread (at least for me)

Thank you

[Søren Dyhr]

Sure.... but it's hardly rocket science

--sd

Bluearrow2.zip

[bluearrow]

Thanks,

It worked this time!