Multiple Records returned in a relationship

Title

Multiple Records returned in a relationship

Post

I have a relationship set up between to tables in two different files. Often, there are more than one record returned that fit the relationship. How do I tell the first file which record I want it to use from the second file. As an example, I have a customer invoice for the first file which automatically grabs pricing information from the second file. Both files use the date to relate to each other as the pricing information is changed daily. My industry has been very volatile lately and I am thinking about changing the pricing information through out the day. I cannot seem to figure out how to tell the Invoice file to grab the most current record in the pricing file.

In your relationship graph, you can double click the relationship line to bring up your relationship options.

Set the sort option for your related record's table occurrence to sort by your date field in descending order. This should put your most recent record first in the found set your Go To Related Record step created.

Another option:

Add a sort [no dialog, restore] step immediately after your GTRR script step to sort the records in descending order by date. That will also make your first record the most recently dated record.

Thank you for the suggestions. My issue is not sorting by date, but by time. I will add that line in the options.

I am concerned that it will change record info on earlier records. I know this sounds confusing. Essentially I have Customer info at the top of my record and lots of complicated formulas for merchandise at the bottom of the record. The pricing info comes from another file that is related to the customer file via date. Everything worked fine until I wanted to update the pricing info more than once a day. I am having a hard time understanding which record of the matching records the formulas grabs the information from. It certainly seems to me that is grabs the first entry of the day. when I add the next entry for pricing and then a new record for invoice, the invoice does not input the most current info. Make any sense?

So if I add the sort step you suggest, will the calculations in the customer record get the most current record in the pricing file? How will that effect the records before it? Will they change as well?

I am thinking that I need another identifier field in the relationship?

Yes, depending on how you've designed your database, this could modify the values displayed in existing records. I can't tell for sure without knowing more about how you've designed your database.

Actually, managing price changes in an invoicing system is a fairly common database task. If you search this forum using the keywords "Quotes" and "invoice", you'll find several threads that discuss this subject. Here where I work, I manage an invoice system that has to handle price changes without changing the values in past invoices.

It sounds like you could benefit from a change in how you've designed your database.

Set up your database so that prices are copied from the price list table into a matching field in one of the tables in your invoicing system. I can't tell from your posts, how you've set this up, but the typical invoicing system consists of an Invoice table where one record = 1 sale, An items table where 1 record = 1 purchased item listed on the invoice and a price list table where the unit price for each item that can be purchased is stored.

Using the above design, you place a portal on your invoice layout to list the items purchased. When an item is entered into the portal, the price is "looked up" from a matching record in the price list table and copied into it. With that approach changes to the unit prices in the Price List table will only affect the next new invoice to be created and the current invoices will remain unchanged.

I will search those terms. In the mean time, here is my situation with more specifics. I have set up a system for buying precious metals. (jewelry, etc.) The first Table is the Customer invoice which collects all of the personal info at the top. This also includes the date and time auto created upon the record. The bottom of the layout is set to invoice each piece the customer wants to sell. It records the item, type of metal, weight of metal. This is a repeating area to allow for 11 items. Then I have calculation fields set up to convert the measured weight to troy oz and from there a calculation that shows how much the item will be worth. I have a relationship set up to match the date on the invoice to the date on the pricing file. The calculations then use that info. All was find when we only had one price per day. With the volatility of the metals markets we are looking at changing the prices twice a day. It seems to me I need a way to relate the invoice record and the price record with more than just date. I looked at open and close price records. This messed all of the invoice records up. I am trying to make this as transparent as I can as I will not be interfacing with the database when it is up and running.

So in my case, 1 customer is one sale, but not one item. They tend to bring in a few things at a time.

I even thought about a calculation between the invoice time and the pricing time with some parameters. Never got that set. At day's end, I set a field which determines AM or PM on the time. This allows for only changing the price morning and afternoon and is the other relationship parameter. It seems to be working. but not a very elegant solution.

Can you explain a little more about the pricing table and how you would look up and copy and paste into a different table? That is something I have never done.

The "precious metals" we buy here are called "Iron, Brass, Aluminum..." :smileywink:

And we definitely have multiple items we buy in each transaction.

"This is a repeating area to allow for 11 items."

Interestingly enough, our invoices allow for exactly 11 items also! I used to have these in repeating fields, but that made for extremely complex issues that I've eliminating by replacing them with a second related table where one record = One item.

Here's how it works:

Table 1: Invoices, with an auto-incremented serial number that uniquely identifies each record. Plus customer fields similar to what you describe.

Table 2: Line Items, records each individual item purchased and contains the calculation fields to use a looked up unit price to compute the extended price for that item.

Table 3: Materials, Lists each metal by name and also gives the unit price for each such item.

The relationships:

Invoices::InvoiceID--=--LineItems::InvoiceID (The allow creation of related records option is enabled for LineItems.)

LineItems::MetalName--=--Materials::MetalName

In LineItems, I have a Number field, UnitPrice, with the auto-enter option selected for a looked up value. The looked up value specifies a UnitPrice field in Materials as the looked up value.

In my invoice layout, I place a portal where you have your repeating fields. The first field in the portal row is a text field for naming the metal and I've formatted it as a drop down menu. When our buyer selects a metal from the drop down menu, the matching unit price is looked up from the Materials table.

If you'd like a very simple demo copy of this concept that I created for another forum user, click my Forum name and send me a private message with your email address and I'll send it to you.