13 Replies Latest reply on Aug 21, 2013 10:18 AM by MichaelArnovitz

    IsEmpty function problem

    MichaelArnovitz

      Title

      IsEmpty function problem

      Post

           I am importing data which contains numerous fields. One of those fields (call it "field A") has records which contain either a number or no content at all. I am running an auto-enter calc on the records in that field, and the results of that calc end up in a new field (call it "field B"). If the records have data I just want that data moved to field B. If the imported records are empty I want to put an arbitrary numer is field B. So I have a simple "IF" function for field B:

           Field B = IF (IsEmpty (field A); 42; field A)  
           /*where "42" is the arbitrary number I throw in if the field for a given record is empty*/

           The script itself is working just fine. The problem is this: if I import data that doesn't even have that field, the "IsEmpty" function apparently considers that to still be an empty record and puts a "42" in field B. I was assuming that this function would know the difference between an empty record and a non-existant one. But I guess not.

           So how do I change my script so that the "IsEmpty" function will only work if the imported data actually has the field in question?

        • 1. Re: IsEmpty function problem
          philmodjunk

               I think that you'll need to use two import records scripts or steps, one with auto-entry options enabled and one without them enabled. THen use the one with auto-enter enabled when the field exists and use the other when it does not.

          • 2. Re: IsEmpty function problem
            MichaelArnovitz

                 Thanks Phil - I've been trying to wrap my ahead around that type of approach, but I'm not sure it will work for what I'm doing.

                 Is there a way instead to include a first step in the script that looks for a field name? Does GetFieldName do this? For example I could have a Case or Let script that begins by looking for a field name. If it doesn't see that given field name then it fills field B with nothing (""), but if it does see it then it does the other calc. What do you think?

            • 3. Re: IsEmpty function problem
              philmodjunk

                   GetFieldName returns the tableOccurrenceName::FieldName text of the TableOccurrenceName::FieldName used as it's parameter. This is data taken from the info in Manage | Database so I don't see how you can use that for what you are doing here.

                   I need a much clearer understanding of what you are doing here. From what file types are you importing data. How can there be one source file where the field does not exist another source file where it does and how can one script successfully import data from both?

                   In many cases, that would create major problems just properly mapping columns of imported data to fields in your target table so I want to be sure that I have a complete picture here of what you have set up and how it works thus far.

              • 4. Re: IsEmpty function problem
                MichaelArnovitz

                     Phil - I am importing files with about 15 possible fields. Unfortunately this is not a situation where every file I import always has every field. The individual files only come with a field if it contains data. Otherwise the file will not even have the field at all. Which is fine. That part is not causing a problem. 

                     The problem is that one of the fields behaves in an atypical manner. Usually a field simply has content or it doesn't. Easy. In this instance I have a field in which some of the records are blank (empty), but that does NOT mean no data or information. It should mean this, but it doesn't. In this one field only, when a record is empty it means instead that it is a given number. So I have to convert that empty field to this number.

                     So why not just put that number in there to begin with? The data comes from a third party, and this is just the way they do it. Weird and frustrating, but there you have it. And yes I could open up each CSV file in excel and just replace the empty records with that number. Or do a replace field contents after each import. But I have to do a lot of these imports so I'm trying to automate this if I can.

                     So I have a weird situation with a CSV file that is giving me blank records that in one field only don't mean what empty should mean. How often do you have to make "nothing" equal "something"? Not often. This is the first time I've ever run across it. And hopefully the last. So I'm trying to figure out a way to cobble together a script that will, in this one field only, turn an empty record into a specific number, but NOT if that field is not part of an import. That last part is what is getting me tripped up.

                • 5. Re: IsEmpty function problem
                  philmodjunk

                       Sorry, but "blank records in 1 field only" makes no sense. A record--a row in the CSV file is made up of potentially many fields--each a column of data in the CSV file.

                       I don't see how, even by opening the file and looking at it, you can tell the difference between a record with a field that is empty and a field that is "missing". They would look exactly the same as far as I can tell from here.

                  • 6. Re: IsEmpty function problem
                    MichaelArnovitz

                         Sorry Phil. I'm not doing a very good job at explaining this. I mean one COLUMN only, not one field. So if you opened the CSV file in excel you would see a row of columns, and in the column in question you would see some rows with data and some empty. Does that make more sense?

                    • 7. Re: IsEmpty function problem
                      MichaelArnovitz

                           Crap, wait. I screwed up again. You would not see entire rows that were empty. You would see fields in that ONE COLUMN that were empty. Some fields in that one column have data, some don't.  

                      • 8. Re: IsEmpty function problem
                        philmodjunk

                             Column = FIeld so I still don't see what difference would be found in the CSV. From what I am reading here, you are still describing the fact that a field (column) is empty and yet that empty field means missing data in one case and that it is a field that doesn't exist in another. I still don't see what visible difference would exist if you opened up the file and looked at the raw data.

                             My understanding here is that in some cases, the entire column is not present. If that's the far right column, that might still work, but otherwise the missing column would mean that some of your data will end up in the wrong field.

                        • 9. Re: IsEmpty function problem
                          MichaelArnovitz

                               Let me try an image. That might help. You'll see that after the address there's a column (call it "Field A") in which some of the cells have data and some do not. When you import data an empty cell is typically easy - it just means no data. In this one instance however it actually means something else, so I have a auto-enter calc that keeps the number as it is UNLESS the cell is empty, in which case it changes it to an arbitrary number.

                               As I said, this works fine. But if I import a file that doesn't even have a column with "Field A", the "IsEmpty" function gives every single record that arbitrary number. I don't want this to happen if there is no "Field A" column at all. I just want it to happen if there IS a "Field A" column.

                          • 10. Re: IsEmpty function problem
                            philmodjunk

                                 But as I understand it, with one import those empty cells mean "empty data replace with 42" and in another case it means "no such field, don't do anything".

                                 But how can you tell the difference?

                            • 11. Re: IsEmpty function problem
                              MichaelArnovitz

                                   I think that is exactly the problem Phil - how does Filemaker tell the difference? The initial answer seems to be that it can't.

                                   Here's what I thought would make the difference: Field Headers. FP can obviously detect the presence of field headers in the import file. That's how it matches those to target fields in the dbase, or at least offers you the ability to map them yourself. So I assumed that if there was no field header for a given field FP would know this and not continue to perform functions on a field that wasn't being imported.

                                   But at least in the case of the "IsEmpty" function FP continues to operate even if that field doesn't exist in the data you import. The only way I can imagine to deal with this would be to write a script that begins by looking for a specific field header in an imported file. If that header exists do thing A, if not do thing B. But I cannot see any obvious way to script this.

                              • 12. Re: IsEmpty function problem
                                philmodjunk

                                     Not only can FileMaker not tell the difference. I haven't been able to tell the difference either. Now that you can  point to a difference in the files that distinguishes one from the other, we can take a look at how to detect that difference and use it to do a different import.

                                     Do two imports, one that imports the data into a table and that imports the header row as data into text fields followed by a second import that correctly imports your data. To avoid importing the entire file, set up a single record in this table's found set and use the "update existing records in found set option". I think that you'll need to make sure that the field where we need to check for an existing column header exists is empty before importing.

                                     Once you have that first import done, you can use IsEmpty to check for data in that specific text field and now you can set up an If block in your script where you import the file a second time, but now into your actual target table and you can specify different Import Records script steps with different settings (one can disable the auto enter field options) to get the resutls that you need.

                                • 13. Re: IsEmpty function problem
                                  MichaelArnovitz

                                       Thanks Phil. Let me wrap my head around this and I'll give it a go. I appreciate you sticking through my bad descriptions of the issue!