Wrong relationship or wrong brain

[Oyseka]

I have an invoice file, line items file and credit note file. If I post a credit I want to lookup the details from the Line items and the Sales File file based on the relationships:

Credit note: invoice No.>Line items:invoice No

Credit Note:Invoice No>Sales File:invoice No.

This allows me to bring in from the Invoice all the customer details including VAT exempt status and order No., and from the Line Items file the price at invoice date as well as the product details.

When I enter the Invoice number in a new record the client data is brought in successfuly from the Sales File but the product data goes belly up. It brings in two of the same items from the Line Items file from different invoices.

[EllenG]

The problem doesn't look obvious from your description. The relationships appear to be correct. Did you look in your Line Items file and verify the Invoice numbers on the items retrieved?

[Oyseka]

EllenG said:

The problem doesn't look obvious from your description. The relationships appear to be correct. Did you look in your Line Items file and verify the Invoice numbers on the items retrieved?

I have attached a screenshot of the line items file to show they are from different invoice numbers, (unfotunately I can only add one attachment otherwise I would attach shots of the Invoice file and the Credit note file

Picture 1.pdf

[Oyseka]

Whoops, that was the Invoice file screenshot, this is the Line items screenshot

Picture 2.pdf

[Oyseka]

Pleeeeease can anybody assist. If I enter invoice number FC0010 in the Credit note file, I get two lines with the product code ALL004 which are from different invoices in the line items file

[Oyseka]

Ah Ha, the thot plickens ?? The nature of the problem is becoming clearer, though not the solution. The lookup from the Credit note to the Line items is not bringing in two items from different invoices, it is in fact duplicating the first item found in the Line Items matching the invoice number by however many line items there are in the Invoice. Why would it do that

[OffTheDeepEnd.com]

Hello,

I do not know if this will help, or make the problem worse, but whenever there is a relationship set up using and invoice id containing a two or more digit text and then a numerical number (i.e. "FL00001"), filemaker will sometimes confuse that with other numbers that maybe in the database such as "SL00001". I have found a lot of problems with starting anything in relationships with 2 letters and then numbers. You may want to try a different numbering scheme. I am not sure if this will help at all, but if not I will check back and see your reply, I was kinda confused at the question.

-Austin

[-Queue-]

This should only be a problem if your relationship fields are defined as number instead of text. FileMaker does not index text in number fields, so FL00001 and SL00001 are considered equal and would be indexed as '1', since no leading zeroes are indexed either. Make sure both of your fields are text, and you will not have this problem.

[Ugo DI LUCA]

Hi,

Not sure I fullly understood how you were relating your credit note to the line item, but if the same item populates each row of the portal, it may be the fields in the portal aren't using the same relationship as the one defined for the portal.

About the lookup, which definitely I didn't understood, a lookup will only retrieve the first matching record, not all of them.

HTH

[Oyseka]

Ugo DI LUCA said:

Hi,

Not sure I fullly understood how you were relating your credit note to the line item, but if the same item populates each row of the portal, it may be the fields in the portal aren't using the same relationship as the one defined for the portal.

About the lookup, which definitely I didn't understood, a lookup will only retrieve the first matching record, not all of them.

The line items are related to both invoice and the credit note by the invoice number such that if I wish to post a credit I first locate the invoice that I wish to post a credit against then move to the credit note file. I then enter the invoice number and the client data is looked up. I then want to enter the particular line item that I want to credit and have a lookup performed on the price of that line item on that particular invoice. I have attached a screen shot of a trial credit note and repeated this post to attach shots of the invoice and the line items. As you can see from the credit note shot as soon as I enter the invoice number the line items brings in a repeat of the first item found matching the invoice number times however many line items were on the original invoice

Picture 1.pdf

[Oyseka]

This is the invoice shot

Picture 2.pdf

[Oyseka]

This is the line items shot

Picture 3.pdf

[Ugo DI LUCA]

Well,

this doesn't give us any clue on your relationship defs, but I'm quite convinced you mad the mistake I reported above, that is that :

- Portal has a relationship named R1

- Fields in portal use another relationship named R2

Check this first. Even if both relationships are *exactly* the same, your portal would have the behaviour you pointed.

[Oyseka]

Hi Ugo, the relationships appear correct, once again I have attached a screen shot showing both the portal and field relationships

Picture 4.pdf

[Ugo DI LUCA]

Again, I must be missing something....

Why is it that you present us a screen shot of a lookup def, while your problem resides in a relationship def for a portal ?

What is that lookup problem you're encountering ?

Any chance you attach some files rather than screenshots, that really offers no way to solve this issue.

I don't have 6 so I wouldn't be able to debug this if needed, but other could for sure.

[Oyseka]

Hi Ugo, I put the file in layout mode so that the portal relationship also shows on the bottom left of the portal (Line Items). If I understand you correctly you felt that the problem was that the relationship of the portal was not to the same relationship of the lookup. I also have FMP 5.5 so I could send the files in that if it would help.

Chris

[MarkWilson]

I have a script (Script A) in a Master file that calls a sub-script (Sub-Script in the Sub file. Sub-Script B calls another sub-script (Sub-Scipt C) in the same sub file. If I run Sub-Script B in it's own file it works flawlessly. When I call it with Script A it skips over the Preview Mode function and performs the Show Message function in the Browse Mode of the Master file.

- If I add a halt script after the Preview Mode function it will halt as requested in the Preview Mode of the Sub file.

- If I add a beep after the Preview Mode it will "beep" and display the Show Message function in the Browse Mode of the Master file.

- If I add a Pause/Resume Script ["1:00:00"] it will work exactly as planned only it takes one extra second.

Why does it not work without some kind of interuption to trigger the Preview Mode?

Script A - Performs Sub-script B

Go to Layout [original layout]

Sub-Script B - Perform Script [sub-script, "Sub-Script C" ]

Preview []

# Comment Line Choice 1

# Comment Line Choice 2

# Comment Line Choice 3

Show Message ["blah,blah blah"]

If ["Status( CurrentMessageChoice ) = 1"]

Print [No Dialogue]

Go to Layout [original layout]

Sub-Script C - Performs Print Setup

[Vaughan]

Mark's post is duplicate, don't respond here.