Creating Record Kills FM

["... you mean these fans?"]

Hi, we ran into an issue last week when we were unable to select certain global fields to where a value list would drop down, basically when the customer clicked into these global fields "No Value List".  We ended up replacing those fields with "New" globals and reassigning the value list.

However, to our discovery, when we try to create a new "Contact" we get the spinning beach ball and when I navigate to the "Force Quit" under the "Black Apple" I see that "Filemaker" is not responding.

I've isolated the issue to one field, this field is a "Sum" field to another table where we sum the field "Total" and sum the field "Paid" and subtract;

Sum ( Contact ID to Invoice Lines Contact ID::Total ) - Sum ( Contact ID to Invoice Lines Contact ID::Paid )

As long as I keep this calculation disable; /*Sum ( Contact ID to Invoice Lines Contact ID::Total ) - Sum ( Contact ID to Invoice Lines Contact ID::Paid )*/ we can create records on our contacts table.  The minute I remove the ... /*, FM is killed.

I've tried creating many new field in our contacts table and build separate calculations but to no avail.  I've tried building new relationship but again to no avail

Any idea what would cause the issue to follow when creating new calculation fields ??

Edited January 31, 2016 by Snozzles

["... you mean these fans?"]

Hi,

I created two additional fields on the invoice lines table ...

Total Calc = Total

Paid Calc = Paid

On my contact Balance Due field calculation I used the Total and Paid Calcs in my "Sum" calculation ...

So the sum calculation look this ...

Sum ( Contact ID to Invoice Lines Contact ID::Total Calc ) - Sum ( Contact ID to Invoice Lines Contact ID::Paid Calc )

I am able to successfully create new records in my contact table for now.

Any insight here, I would be grateful.

Thank you Tom :-)

 

[Wim Decorte]

This is a classic scenario where you want to step away from calculations and set these values in a truly transactional manner.  Yes: it's a lot more scripting, but it is safer as values only will get updated when everything is ok, and it is a lot faster since you are storing only the end values and the calcs are done by your script, not by the field at the schema level with all of the slowdown that comes from every displaying those fields or trying to report on them.

["... you mean these fans?"]

Hi Wim Decorte,

48 minutes ago, Wim Decorte said:

This is a classic scenario where you want to step away from calculations and set these values in a truly transactional manner.

I've discovered that fields that look through a relationship are not available for value list and I have to anticipate these value list and "write" the data ... especially a "Contact Name" customers demand that they search these fields by typing in the customer name, if I don't write the name to this field the value list I need to use will not work.  The only challenge I have with this, is that I am now forced to NOT allow a contact to have the same name, which now I have to discuss this issues and others with clients that have many "ABC Construction Company's" or individuals named "John Smith".

 

48 minutes ago, Wim Decorte said:

but it is safer as values only will get updated when everything is ok, and it is a lot faster since you are storing only the end values and the calcs are done by your script, not by the field at the schema level with all of the slowdown that comes from every displaying those fields or trying to report on them.

Yes, through the pain and pleasure process Filemaker provides I am having to write my data now to where I use to have relationship providing the data to the field.

So, can you clarify a bit more.

My body senses your answer to be very correct based on again my "Pain and Pleasure" Filemaker provides.  Are you saying that I write this "Sum" data to the field each time my customer performs a find for a "Contact" ?

And if my customer performs a find for many contacts I would need to loop through the "Found Set" and write the "Balance Due" value via a "Set Field" script step.

And how would this work if my customer wanted a portal ?? I would have to loop through the found set from within the portal ... yes ??

I'm discovering that a "Relational Database Model" is not very "Relational" since all my data I need is unavailable due to the inability to "Index" my value.  So I end up having to write the data which negates having a "Relational Database" in the "True Sense" of the word.

Thank you.

Tom

Edited January 31, 2016 by Snozzles

[bruceR]

Consider taking an extended class or paying for a mentor so that you eventually are able to speak realistically about effective development theory and practice.

[LaRetta]

4 hours ago, Snozzles said:

I've discovered that fields that look through a relationship are not available for value list and I have to anticipate these value list and "write" the data ... especially a "Contact Name" customers demand that they search these fields by typing in the customer name, if I don't write the name to this field the value list I need to use will not work.  

Hi Tom,

Can you provide a specific example?  I ask because many of your statements in your post are incorrect in a normalized database structure.  The need to write data to the field, as Wim indicates, only involves summarized data which must be unstored.  Using transactional methods to write the data to fields instead of relying on aggregate calculations is a specialized process and does not include any of the examples you've mentioned.

I suspect you've established some incorrect beliefs.  If you wish to explore them further, it would be good to create a new post about each of them and we can break the myths apart.  Once you are clearer on how FileMaker really works, you will feel much more comfortable with it.  :-)

["... you mean these fans?"]

 

As usual Bruce, thank you.

Not everybody come into this physical illusion complete.  Those that do I wish them well.

Unfortunately I need a parent / child relationship.  And even that can be very confusing.

What you mean is a "Coach" not a "Mentor".  There's an a very unique difference between the two.

As far as consultants go, I've stopped giving my money to them because they only see the value in one direction, they only see what "THEY" bring to the table not what I bring to the table "My Money".  Again I am grateful for your comment.

Tom

[comment]

46 minutes ago, LaRetta said:

I suspect you've established some incorrect beliefs.

Indeed, so do I.

 

25 minutes ago, Snozzles said:

they only see what "THEY" bring to the table

I am looking for a well-known proverb, something about a "mote"?

["... you mean these fans?"]

LaRetta,

Yes I come to the FMForum angry and frustrated because I've been working the solution and reading white pages having no real answers so I make a post.  If I am offending those who come into this physical universe complete, I mean no disrespect that's for sure.

I wish that some of the member step back and appreciate how long a person is trying to resolve issue on their own ... I avoid these forums as long as possible, LaRetta.

LaRetta, you've always treated me with kindness and I will always be grateful for that.

I have a unique understanding about this phase now ... "The road to hell is paved by good intentions". 

When I worked for "Verizon" after having to suffer through a failing business that I did everything I could to save, including giving money to people who said they could help me while not really having the funds to pay for these professional service and they never saw my money ... Verizon made an very interesting statement that I take with me now through my life:

As people within Verizon where complaining why the "Customer Service" agents were not telling the customer that "Verizon" can't do that, and believe me there was a lot of angry people in that room that day, this one individual stood up and said "Hey people, It is NOT Verizon's Job to say NO".  That comment was amazing to me.

It is not my job to say "No" to my customers, my job is to try and find a why to provide a solution that "THEY" want ...

So, my customers say to me ... Tom, do not put the ID in the value list, please hide it.  So I do, then they say to me ... Tom, why can't I type the customer name into this value list as shown on the web page video they just watched ?? See Tom look and they push my face into the computer, I say are you kidding me .. but I remember what that one guy in Verizon said and keep my mouth shut.

So now what do I do.  Currently my understanding of a "Number Field" and a "Text Field" in a "Filemaker" value list will not allow me to type "Text" ... so I create a relationship to the "Table" by the "NAME" and I know this is not a very good way, but this is what my customer want.

So ... by now I've also discover that a value list will only list the value "ONCE" so 43 "John Smith" or 43 "ABC Rental Co." will only list "Once" in the value list.  And I also discover that by how I sort the relationship the value list is looking so "John Smith" or "ABC Rental Co." gives me either the "First" ID or the "Last" ID depending on the sort.  So now the customer say Tom, where are my other 42 John Smith or ABC Rental Co. ?.  

Now I try to explain to the customer that we will have to edit the 43 customer to be "Unique" since you don't want me to use the ID fields and Filemaker will not allow me to pull the value list and type the name and please everybody I understand what restriction filemaker is in and I completely understand it.  I am not asking to be unrealistic, all I'm say is that my customer work flow are dictating how the solution flows a certain way.

I'm not being "Unrealistic" Bruce! I am just working toward satisfying my customer requirements.

So again LaRetta, I am grateful for your kindness.  Thank you.

 

Tom

 

 

Edited January 31, 2016 by Snozzles

[LaRetta]

 

2 hours ago, Snozzles said:

So ... by now I've also discover that a value list will only list the value "ONCE" so 43 "John Smith" or 43 "ABC Rental Co." will only list "Once" in the value list.  And I also discover that by how I sort the relationship the value list is looking so "John Smith" or "ABC Rental Co." gives me either the "First" ID or the "Last" ID depending on the sort.  So now the customer say Tom, where are my other 42 John Smith or ABC Rental Co. ?.  

Hey Tom, yes it can be frustrating for sure.  But with your example of customers, have you ever posted here with that question?  The answer is simple ... no, customer name does not have to be unique nor does it have to be copied to other tables as you suggest.  Instead, you include something in the string which tells your User WHICH John Smith to choose, such as their city or phone and base your value list display on that field instead.  "Are you the John Smith on Irving street or South 42nd?  There are simple answers to most everything you said was frustrating ... please just ask us.

2 hours ago, Snozzles said:

Yes I come to the FMForum angry and frustrated because I've been working the solution and reading white pages having no real answers so I make a post.  If I am offending those who come into this physical universe complete, I mean no disrespect that's for sure.

I wish that some of the member step back and appreciate how long a person is trying to resolve issue on their own ... I avoid these forums as long as possible, LaRetta.

No offense taken, Tom.  But avoiding FMForums and NOT asking questions is not the answer at all.  I came to FileMaker with Fortran background but I had to start from scratch with FileMaker.  I also had relational theory under my belt and that helped a bit but 98% of everything I know has come from studying these forums and asking questions.  Absolute truth.

Believe me, most of us here completely understand where you are coming from.  From the few developers who have answered here, you have ( without question ) at minimum 50 years of full-time developer experience designing for ( again at minimum ) easily 500 customers.  We KNOW the struggle you go through and most of us do NOT judge you harshly for misinterpretations or mistakes.  

2 hours ago, Snozzles said:

I am not asking to be unrealistic, all I'm say is that my customer work flow are dictating how the solution flows a certain way.

I'm not being "Unrealistic" Bruce! I am just working toward satisfying my customer requirements.

You are not being unrealistic in wanting to satisfy your customers but you ARE being unrealistic to ALWAYS satisfy them. If your customer says they want the database to make them coffee in the morning, is it your job to tell them NO?  Absolutely.  YOU are the expert here and, it may have been Verizon's viewpoint ( or that manager's viewpoint) to never say no but that has nothing to do with you and your business.  Your job is to put yourself in the customer's shoes and do what is best for them - NOT VERIZON and that may involve disagreeing with them at times, always explaining why it won't work and finding other approaches.  We do it on occasion as well.  It is never fun but it is with clear conscience.

Anyway, I encourage you to post more often and get our support and really listen to our suggestions ... don't think, "well, they don't know MY situation."  I assure you we probably do and always feel free to fill in more details to clarify.  Most of us here get paid by regular clients for the advice we are handing out here for free so give our answers some weight and ask questions when we are unclear or explain more when you disagree and allow us to explain why or offer alternatives as well.

We are here to help.  When I first started in FileMaker, I worked for huge company and worked myself into quite a mess, LOL.  If it wasn't for folks here, I would have sunk long ago.  Stay plugged in, okay?

Edited January 31, 2016 by LaRetta

[Wim Decorte]

16 hours ago, Snozzles said:

As far as consultants go, I've stopped giving my money to them because they only see the value in one direction, they only see what "THEY" bring to the table not what I bring to the table "My Money".

You do realize that those consultants are by and large many of us, right?  And that besides doing our usual consulting we spend time here giving free help to further the community...

 

15 hours ago, Snozzles said:

Yes I come to the FMForum angry and frustrated because I've been working the solution and reading white pages having no real answers so I make a post.  If I am offending those who come into this physical universe complete, I mean no disrespect that's for sure.

I wish that some of the member step back and appreciate how long a person is trying to resolve issue on their own ... I avoid these forums as long as possible

 

 

Maybe you should NOT avoid the forums and perhaps avoid being angry and frustrated.  It shows in your posts and it does not really help.  Of course we don't know what you have been through and tried already.  We come in cold and have only your description to work with.

Believe it or not but everyone here is trying to help you.  Shooting them them on their choice of words is ...well... unkind.

It is not easy to get through the learning curve of any platform, but the sooner you ask for help the better.

 

15 hours ago, Snozzles said:

So ... by now I've also discover that a value list will only list the value "ONCE" so 43 "John Smith" or 43 "ABC Rental Co." will only list "Once" in the value list.  And I also discover that by how I sort the relationship the value list is looking so "John Smith" or "ABC Rental Co." gives me either the "First" ID or the "Last" ID depending on the sort.  So now the customer say Tom, where are my other 42 John Smith or ABC Rental Co. ?.  

 

 

There are other ways to present lists of things to let the user select from so perhaps you are a little too focused on the value list widget... 

21 hours ago, Snozzles said:

Hi Wim Decorte,

My body senses your answer to be very correct based on again my "Pain and Pleasure" Filemaker provides.  Are you saying that I write this "Sum" data to the field each time my customer performs a find for a "Contact" ?

No, not at all because you are talking about a summary of data not the real data.

What I was saying is that you should script the process of invoicing and applying payments to invoices so that you can avoid unstored calcs for things like "total amount paid" and "unpaid balance" etc, on an invoice basis.

Then when you need to report on range of invoices and use summary fields for that (or a virtual list/table) then that report will be very fast.  As opposed to very slow where FM would need to recalculated all your unstored calculations before being able to summarize the numbers.

[Fitch]

I completely agree with Wim. When a payment is posted, you need to update the stored balance for that account. When an invoice is posted, you also update the stored balance. (And maybe other fields such as previous balance, etc.)

This does require that you tightly script those processes, but it's really the only way to go. If you stay with unstored calculations, the performance will get worse and worse as your record count grows.

By the way, this isn't unique to FileMaker, it's in the nature of relational databases.

[rob]

snozzles, you will not find a better more knowledge group of people to help you than right here.  your frustrations are not their fault and I assure you that you would be much greater frustrated without their fabulous help.

I meant to mention because it draw my attention to the post but your avatar is a bit mean.  I might be scared to respond to you with that looking at me.

["... you mean these fans?"]

Thank you Rob.

[comment]

On 02/01/2016 at 11:27 PM, Fitch said:

this isn't unique to FileMaker, it's in the nature of relational databases.

I am not sure that's a good way to put it. Because actually denormalization goes against the nature of relational databases. Ideally, a relational database would stay normalized. It is only performance considerations that force denormalization upon us.

I think it's important to state this, because it also defines the limits for denormalization. The extent of denormalization should be proportional to the extent of the performance issues encountered without it. it is not something you should reach for automatically.

[Fitch]

Point taken. It's not the nature of relational database theory. But it is the reality of relational database sytems.