How do I loop across related records?

[J__]

Hi,

I have a application which recieves invoices from people who we have to pay.

These people are in companies.

so i have an invoices table, an invoice Line items table, a Project table, a ProjCompany and Companies table. The companies table is used in the invoices to do a lookup for the company.

A project can have one or more companies working on the project. so Ihave a 1-M for that.

the line item then does a look up for the project given a project number. I then enter an amount.

I then click 'Save' and I want to somehow update the ProjCompany table's field Status to be paid for all the records that match the CompanyID and projectID in the ProjCompany table.

I just don't know how to do a find on those records in a script and update that field.

I looked at Record/Request but that seems to operate only on the current table, i need to be able to search on a table, which is not currently in the layout.

I know this isn't the perfect solution, but for now this table structure is the approach i must use.

it seems like find might work, but how do i make it use a different table than the one I am in ?

suggestions tips or solutions would really really really be appreciated.

thanks,

J__

[Ugo DI LUCA]

Hi,

Draw a relation from LineItems to a new occurrence of the CompanyProject using your 2 keys. As I understand it, this should match a unique record in CompanyProject Table.

Now, the script would just perform a SetField[CompanyProject:paid;"paid"] if you're doing it from the line items, or, if you're using a portal from Project, then either :

- go To the Line Items first, then perform the SetField[ ] above

or

- Draw a new relationship from Project to CompanyProject using 2 global fields gCompanyID and gProjectID in the Project Table.

Then from the Line Item portal row.

SetField[gCompanyID; LineItems::CompanyID]

SetField[gProjectID; LineItems::ProjectID]

CommitRecord/Requests

SetField[CompanyTableByGlobals::Status;"paid"]

If it is a loop you want, then do it from the LineItems as it is more comfortable.

[J__]

Thanks so much for the response.

Actually, the lineItem has a 1-M relation with the Projects table.

The Projects Table then has a 1-M relation with the CompanyProject table.

how would it work in that case?

thanks again for the response,

thanks,

sincerely,

J__