Tyson Posted March 25, 2005 Share Posted March 25, 2005 Ok I have 2 tables im working with invoice and payments. They are related by customer id and sale id. The problem is I need a portal to relate from payments to invoice that shows sales greater 0 and the customer id. That way it will show only the sales that need to be paid. Im working in the payments layout. For some reason if i make a new relationship customer id = customer id and relate open sale = relate which are both values 1. If i just relate by customer id it shows all sales i cant get it to just relate the open sales. Any help would be greatly appreciated. -Tyson Link to comment Share on other sites More sharing options...
FileMakin' Tom Posted March 25, 2005 Share Posted March 25, 2005 Left SIde: Text Calc: Case(InvoiceBalance = 0, "", Customer ID & "X") Right Side: Constant Text Calc: Customer ID & "X" Will create a relationship which shows only those records with an balance equal to or greater than .01 for the customer ID in question. Works for me . . . Tom Link to comment Share on other sites More sharing options...
Tyson Posted March 25, 2005 Author Share Posted March 25, 2005 Hey tom thanks for the post. Well i did that and basically what u are telling me is what i already had exept i used 1 instead of X. But I did it your way anyway. I look at the calcs And the fields on both side come out the same resut. Hints it should relate, but it doesn't. I am on the payments layout and in the portal im specifing the new relationship. The fields I place on the portal I used that relationship. Is there something im missing. To me this isnt rocket science but ive just run out of solutions. -Tyson Link to comment Share on other sites More sharing options...
Ron K Posted March 28, 2005 Share Posted March 28, 2005 I don't understand why the portal has to have a customer ID in the relationship. You just want to see all the sales with no payment yet, correct? Irrespective of which customer per se it is. If so you need a third table, more or less a viewer. In your invoice you create a field that has a value if the payment is "0" Then on your viewer table you have a field that you set to that same value and in the portal you have the customer ID show up So in Invoice, This field has the calculation Case(Payment =0;"Show") Now on the Viewer table you have a field "View" and you set "View" to contain the word "Show". In the portal you inclued customer ID and whatever other information you want. Wouldn't that work? Ron Link to comment Share on other sites More sharing options...
Tyson Posted March 28, 2005 Author Share Posted March 28, 2005 Actually This is what i want specifically. Ok I have a Table Invoice which is related to payments by (sale)id sale = (Payments) id sale. Ok that is fine but then from payments i have a new relationship Invoice3 that relates from payments to invoice 3 relationship is (payments) Id Customer & 1 = (Invoice 3) id customer & if (you owe is >0,1,0) This basically to my knowledge should relate because id customer and 1 is on payments side and so is id customer and 1 on the invoice side. ive looked at the calcs the numbers match up everything is good. Im just not gettin the data to relate in the portal on the payments layout im using invoice 3 to relate the data and invioce 3 for the fields am i doing something wrong ??? -Tyson This is really starting to get to me!! Link to comment Share on other sites More sharing options...
The Digital Man Posted March 28, 2005 Share Posted March 28, 2005 I ran into a similar problem and it took me awhile to figure it out. But I think your problem is the table you are basing the relationship off of. I am unclear as to exactly how your structure is. Could you clarify that? For example this is what I get from your post but I INVOICE InvoiceID SaleID PAYMENTS PaymentID SaleID RELATIONSHIPS INVOICE::SaleID = PAYMENTS::SaleID Something tells me I don't have your structure exactly right but I feel confident if I see the exact structure you have I can help. I might be wrong but list out the tables including the Table Occurrences and your relationships like I've done above. Then I'll try and help. Link to comment Share on other sites More sharing options...
Tyson Posted March 28, 2005 Author Share Posted March 28, 2005 Got It. Okay For future references if you have some person poast something that he thinks he knows what he is doing and forgets that he cant relate an un-indexed field. The field i was relating couldnt be indexed therefor could not relate the data i needed. So Most fields Calcs cant be indexed my problem. -Tyson AFTER ALL THAT NO INDEXING... I think I might go sit in the corner and cry!!! Link to comment Share on other sites More sharing options...
By Soliant Consulting,
By Soliant Consulting,
By Soliant Consulting,
Recommended Posts
Archived
This topic is now archived and is closed to further replies.